CODEWITHSUNDEEP
sqloracle-database
Rabid
Rabid

Reputation: 326

Oracle SQL joins with a many to one relationship

Thanks in advance for any assistance you might be able to give. I think I may be asking a trivial question but I cannot determine how I would do this nor what exactly to search for. So my apologies if your time is wasted.

So I have two tables TableA and TableB and I need to join the two sets of data in a specific manner that I will explain below with reference to some example tables.

TableA:

Name | Contact_1 | Contact_2 | Contact_3 
----------------------------------------
Joe  | Anne      | Sue       | Phil
Dan  | Tom       |           |          
May  | Jeff      | Pete      | Tim

etc.

TableB:

Name | Contact_No
-----------------
Anne | 123456789
Sue  | 234567891
Phil | 345678912
Tom  | 456789123
Jeff | 567891234
Pete | 678912345
Tim  | 789123456

Resulting Table when Joining between TableA and TableB on:

TableA.Contact_1 = TableB.Name AND TableA.Contact_2 = TableB.Name AND TableA.Contact_3 = TableB.Name

gives:

Name | Contact_1 | ContactNo_1 | Contact_2 | ContactNo_2 | Contact_3 | ContactNo_3
----------------------------------------
Joe  | Anne      | 123456789   | Sue       | 234567891   | Phil      | 345678912
Dan  | Tom       | 456789123   |           |             |           |
May  | Jeff      | 567891234   | Pete      | 678912345   | Tim       | 789123456

This is just a trivial example to explain my problem. Sorry for such a lengthy explanation but I would rather have a detailed example to limit confusion.

Thanks!

Upvotes: 0

Views: 1050

Answers (1)

Fabian
Fabian

Reputation: 2982

You have to join TableA with TableB three times:

select ta.name, 
       ta.contact_1, tb1.contact_no, 
       ta.contact_2, tb2.contact_no, 
       ta.contact_3, tb3.contact_no 
from TableA ta
     left join TableB tb1 on ta.contact_1 = tb1.name
     left join TableB tb2 on ta.contact_2 = tb2.name
     left join TableB tb3 on ta.contact_3 = tb3.name;

If there are null values allowed in TableA.contact_1 (_2, _3), then you need left joins.

EDIT

Notice, however, that it is usually better to have a separate table that keeps the fact that a pair (name, name) is connected, e.g.,

create table TableA(a_name varchar primary key);
create table TableB(b_name varchar primary key, 
                     b_contact_no varchar);
create table Contacts(a_name varchar,
                      b_name varchar,
                      primary key(a_name,b_name),
                      foreign key(a_name) references TableA(a_name),
                      foreign key(b_name) references TableB(B_name));

That way, you can model an arbitray number of contacts without having to change your database schema.

Upvotes: 2

Related Questions