[SOLVED] MySQL join question...should be simple

[ravageiwise]

This really should be simple but I can't seem to get it figured out.

 

I have 2 tables:

companies:

id: autonumber

name: string

 

contractor_link:

id: autonumber

contractor1_id: bigint not null

contractor2_id: bigint can be null

contractor3_id: bigint can be null

 

Don't give me static on the names of tables or fields as I know these aren't ideal but it's what was supplied to me.

 

Here is an example of 2 rows of data in the company_link table:

1 1 NULL NULL

2 5 9 13

 

What I need to do is join these tables in an SQL statement to pull the names of all 3 company names (contractorX_id matches to companies.id if not null) and the contractor_link id in a single row. So my result would look something like this:

 

1 CompanyA

2 CompanyE CompanyI CompanyM

 

Sadly it does not look like UNIONs work in my case and I can't seem to figure out the JOINS to make this work. Any help is appreciated. Thanks!

[ravageiwise]

I actually solved this shortly after posting:

 

SELECT contractor_link.id as linkid, companies1.name as contractor1, companies2.name as contractor2, companies3.name as contractor3

FROM contractor_link

LEFT join companies as companies1 on contractor_link.contractor1_id = companies1.id

LEFT JOIN companies as companies2 on contractor_link.contractor2_id = companies2.id

LEFT JOIN companies as companies3 on contractor_link.contractor3_id = companies3.id

 

[fenway]

This would be much easier if you have a contractors table too... and normazlied as well.