By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

to avoid cross join while doing inner join

atksamy
P: 91
I have 2 tables like which i would like to query to form a new table.

Expand|Select|Wrap|Line Numbers
  1. table 1
  2.  
  3.      number         type serial index
  4.     1000001      613    3               1
  5.     1000001      613    3               1
  6.     1000001      613    3               1
  7.     1000001      613    3               1
  8.     1000001      613    4               1
  9.     1000001      613    3               1
  10. table 2
  11.  
  12.         number         type serial  index
  13.         1000001      613        3               2
  14.         1000001      613        3               3
  15.         1000001      613        3               4
  16.         1000001      613        3               4
  17.         1000001      613        4               2
  18.         1000001      613        3               1
i am looking for a query which gives a resultant of indexes and number like this and adds teh index and give s the result

Expand|Select|Wrap|Line Numbers
  1. output table
  2.  
  3. number                index
  4.         1000001     3
  5.         1000001     4 
  6.         1000001     5 
  7.         1000001     5 
  8.         1000001     3
  9.         1000001     2
I want to prevent cross join here i cant use where clause because the numbers are same in both tables only the index varies. how can i execute this one to one and not one to many
Feb 17 '09 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,398
I expect the tables can be joined in a suitable way. However, I really don't follow what you are using to determine which records match and which do not. Certainly, the matching criteria are not even displayed in your required output (which is likely to make it confusing to anyone that sees it).
Feb 17 '09 #2

atksamy
P: 91
Actually this is a bizaare query but there is no condition i mean if i use where clause using the number then it will be a cross join which i dont want. i only want the first line of table 1 to be processed with first line of table 2 and then second line of table 1 with second line of table 2 and so on... to process the index values
Feb 17 '09 #3

NeoPa
Expert Mod 15k+
P: 31,398
In that case this is not really database processing as such. It can be done in SQL if there is a field in the table which reflects the order required. Otherwise it can only be done in code.

This sounds to me very strongly that the design of your database needs some serious looking into. Check out Normalisation and Table structures for further info on design whys and wherefores.
Feb 17 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.