SQL fetch multiple values on join

Question

Hi I have an SQL table which has two tables which make reference to the same foreign key in a separate table twice... something like

SALES table

idSales idClient1 idClient2
1       1         2

CLIENT table

idClient ClientName
1        Bob
2        Mick

I want to join the SALES table to the CLIENT table and return data as follows:

idSales idClientClientName1 idClientClientName2
1       Bob                 Mick

Can anyone help with the SQL for this? I'm getting ambiguous column name errors on my join.

Thank you

Answer 1

You need to basically join table Client on table Sales twice because there are two columns on table Sales that are dependent on table Client.

SELECT  a.idSales,
        b.ClientName ClientName1,
        c.ClientName ClientName2
FROM    Sales a
        INNER JOIN Client b
            ON a.idClient1 = b.idClient
        INNER JOIN Client c
            ON a.idClient2 = c.idClient

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

But when one of the columns or both columns are nullable, INNER JOIN will not give you all records from Sales because it will only select where it has atleast one match on the other table. Instead use LEFT JOIN.