| Author |
Topic |
|
newbie007
Starting Member
37 Posts |
 Posted - 2009-04-07 : 07:49:45
|
| I have 2 tables (T1 & T2). T1 has columns - name,id,homeno,jobstarttime,jobendtime etc and T2 has homeno,gender,age group . I am joining the 2 tables using enterprise manager on common homeno (inner join). But the total number of rows of the joined table is greater than the number of T1 rows.Since the joined table is retrieving only gender,age info from T2, how is this possible - shouldnt it be equal to TI rows |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-07 : 08:00:34
|
| It can be greater if there are duplicates on homeno in table2. |
 |
|
|
newbie007
Starting Member
37 Posts |
Posted - 2009-04-07 : 08:12:01
|
quote:
Originally posted by sakets_2000
It can be greater if there are duplicates on homeno in table2.
1)how can i retrieve these additional entries ?2)I need all entries of T1 + only the corresponding gender,age data from T2 - how can i get this ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 09:56:57
|
quote:
Originally posted by newbie007
quote:
Originally posted by sakets_2000
It can be greater if there are duplicates on homeno in table2.
1)how can i retrieve these additional entries ?do you mean columns of t2?2)I need all entries of T1 + only the corresponding gender,age data from T2 - how can i get this ?as evident from result, you've multiple records existing from t2 for some homeno. in that case what value you want to return for each homeno in t1
|
|
|
|
newbie007
Starting Member
37 Posts |
Posted - 2009-04-07 : 11:25:38
|
quote:
Originally posted by visakh16
quote:
Originally posted by newbie007
quote:
Originally posted by sakets_2000
It can be greater if there are duplicates on homeno in table2.
1)how can i retrieve these additional entries ?do you mean columns of t2?2)I need all entries of T1 + only the corresponding gender,age data from T2 - how can i get this ?as evident from result, you've multiple records existing from t2 for some homeno. in that case what value you want to return for each homeno in t1
1)additional entries : joined table entries over and above TI data.If T1 has only n data points,what TI values do the additional entries in the joined table correspond to ? You mentioned i have multiple records in t2 - can i identify these home nos2)home nos are unique, & 1 home no will map to only 1 id,age,gender etc. so against each home no there shd be only 1 value of gender,id,age etc. how to join without duplicate entries ?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 11:30:52
|
quote:
Originally posted by newbie007
quote:
Originally posted by visakh16
quote:
Originally posted by newbie007
quote:
Originally posted by sakets_2000
It can be greater if there are duplicates on homeno in table2.
1)how can i retrieve these additional entries ?do you mean columns of t2?2)I need all entries of T1 + only the corresponding gender,age data from T2 - how can i get this ?as evident from result, you've multiple records existing from t2 for some homeno. in that case what value you want to return for each homeno in t1
1)additional entries : joined table entries over and above TI data.If T1 has only n data points,what TI values do the additional entries in the joined table correspond to ? You mentioned i have multiple records in t2 - can i identify these home nos2)home nos are unique, & 1 home no will map to only 1 id,age,gender etc. so against each home no there shd be only 1 value of gender,id,age etc. how to join without duplicate entries ?
1. those entries will from table 22. if homeno is unique and you join only on homeno then you will get only 1 record corresponding to each record in t1 so that result set will have same number of rows as t1. so either your table has multiples per homeno or you're not joining properly. |
|
|
|
newbie007
Starting Member
37 Posts |
Posted - 2009-04-07 : 11:47:03
|
| 1. those entries will from table 22. if homeno is unique and you join only on homeno then you will get only 1 record corresponding to each record in t1 so that result set will have same number of rows as t1. so either your table has multiples per homeno or you're not joining properly.[/quote]the distinct home nos in T2 > distinct home nos in T1. but all the TI home nos are part of T2. Is the problem bcos of this? How can i delete all non-T1 home nos from T2? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 12:54:14
|
quote:
Originally posted by newbie007
1. those entries will from table 22. if homeno is unique and you join only on homeno then you will get only 1 record corresponding to each record in t1 so that result set will have same number of rows as t1. so either your table has multiples per homeno or you're not joining properly.
the distinct home nos in T2 > distinct home nos in T1. but all the TI home nos are part of T2. Is the problem bcos of this? How can i delete all non-T1 home nos from T2?[/quote]nope. even if that case t1 join t2 on homeno will return only homenos in t1 |
|
|
|
|
joined table rows