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

Multiple table join issue (Oracle)

P: 2
Greetings. I'm a first time poster, so feel free to clue me in to any protocol I may have inadvertently violated.

I have an issue that has arisen where I need to join 3 tables where 2 of the 3 contain ambiguous data.

Table 1 is the entity with PK as a primary key.
Table 2 is using PK as a foreign key.
Table 3 is using PK as a foreign key.
Tables 2 and 3 are only related through that foreign key and contain dissimilar data that relates to Table 1.
The relationship between table 1 & 2 and 1 & 3 are both one to many.

There's no problem pulling all the data using a 3 table join, but the issue is getting rid of the duplicates created by the joins (2 & 3 can contain any number of records.) I don't want to run additional queries inside of a loop to get this data, because I'm sure there is a more elegant solution and I believe in letting the database do its' job.

Here is an approximation of the data if that will help:

table 1
PK, field_1, field_2
1, foo1, foo2

table 2
FK, field_1
1, fooA
1, fooB
1, fooC

table 3
FK, field_1, field_2
1, fooX1, fooX2
1, fooY1, fooY2

There must be a way for me to pull table 1 and only the matching records from table 2 and 3 without duplicates.

Thanks in advance for your time!
Feb 27 '07 #1
Share this Question
Share on Google+
2 Replies


vijaydiwakar
100+
P: 579
Greetings. I'm a first time poster, so feel free to clue me in to any protocol I may have inadvertently violated.

I have an issue that has arisen where I need to join 3 tables where 2 of the 3 contain ambiguous data.

Table 1 is the entity with PK as a primary key.
Table 2 is using PK as a foreign key.
Table 3 is using PK as a foreign key.
Tables 2 and 3 are only related through that foreign key and contain dissimilar data that relates to Table 1.
The relationship between table 1 & 2 and 1 & 3 are both one to many.

There's no problem pulling all the data using a 3 table join, but the issue is getting rid of the duplicates created by the joins (2 & 3 can contain any number of records.) I don't want to run additional queries inside of a loop to get this data, because I'm sure there is a more elegant solution and I believe in letting the database do its' job.

Here is an approximation of the data if that will help:

table 1
PK, field_1, field_2
1, foo1, foo2

table 2
FK, field_1
1, fooA
1, fooB
1, fooC

table 3
FK, field_1, field_2
1, fooX1, fooX2
1, fooY1, fooY2

There must be a way for me to pull table 1 and only the matching records from table 2 and 3 without duplicates.

Thanks in advance for your time!
select field_1,null from table1 t1,table2 t2 where t1.pk=t2.fk
union
select field_1,field_2 from table1 t1,table3 t3 where t1.pk=t3.fk
order by 1

this qry may solve thy problem
if not then give me thy tbl desc with some actual data
Feb 28 '07 #2

P: 2
select field_1,null from table1 t1,table2 t2 where t1.pk=t2.fk
union
select field_1,field_2 from table1 t1,table3 t3 where t1.pk=t3.fk
order by 1

this qry may solve thy problem
if not then give me thy tbl desc with some actual data
It absolutely did solve the problem, and I learned something as well. I considered using a union, but didn't realize that you could select nulls to pad fields in the unioned query.

Thanks so much!
Feb 28 '07 #3

Post your reply

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