CODEWITHSUNDEEP
sql
Brian Branco
Brian Branco

Reputation: 11

SQL Query Multiple Joins to same table

I have the following table

Address Table

Code   Company Name    Type   Address Line 1    Address Line 2  City   State  ZIP
ABC01  ABC Company     B      123 Bill To 1     Bill to Apt 2   Newark NJ     12345
ABC01  ABC Company     S      425 Ship To 1     Ship To Line 2  Edison NJ     44445
DEF01  DEF Bill To     B      993 Bill To 1     Bill to Apt 2   Newark NJ     12345
DEF01    
DEF Ship To     S      456 Ship To 1     Ship To Line 2  Edison NJ     44445

Invoice Table

Invoice #   Code    Bill To Name    Ship to Name
12345       ABC01   ABC Company     ABC Company
12346       DEF01   DEF Bill To     DEF Ship To

I need the following results

Invoice #  Code   Bill To Name  Ship To Name   Bill to Address Line 1   Ship to Address Line 1
12345      ABC01  Bill To 1     Ship To 1      123 Bill To 1            425 Ship To 1
12346      DEF01  DEF Bill To   DEF Ship To    993 Bill to 1            456 Ship To 1

Basically I need to join the Invoice table to the address table based on the code + Company Name + Type in 2 different joins.

I am stuck with the query and cant figure how to join them together.

Upvotes: 0

Views: 187

Answers (1)

Omar Tammam
Omar Tammam

Reputation: 1304

for answering your question you can use the following query to get the required result :

SELECT invoice.`Invoice #`,invoice.Code,
ba.`Company Name` as "Bill to Name" ,sa.`Company Name` as "Ship to Name" ,
ba.`Address Line 1` as "Bill to Address Line 1", sa.`Address Line 1` 

FROM `invoice` 
 JOIN address as ba on ba.`Company Name` = invoice.`Bill To Name` and ba.Type = 'B'
 JOIN address as sa on sa.`Company Name` = invoice.`Bill To Name` and sa.Type = 'S';

Upvotes: 0

Related Questions