joining sales header table to (2) different sales line tables

[awholtsIT]

SQL 2005

we have a situation where we have a master sales header table that holds all sales records, however, we have (2) sales line tables, with unlike data structures that hold the aggregate of the sales lines that are associated with the headers.

table A = sales header (3 records, 2 from B and 1 from C)
table B = sales lines (2 records)
table C = sales lines (1 record)

I know how to join A to B, or A to C.

I don't know how to join all (3). The final record set should contain detailed sales lines for (3) records.

Appreciate any tips or assistance with the logic to accomplish this.

Thanks,

Andrew

 

[markros]

I believe for your case you need to use UNION ALL, e.g.

select A.*, B.* from TableA A inner join TableB B...
UNION ALL
select A.*, C.* from TableA A inner join TableC C...

Make sure the fields from B and C match (or make them match).