SQL Server Performance Forum – Threads Archive
Joining issueHello, I would like to know how I join 3 tables Table A -> Table B
-> Table C Where table A : Sale Headers
B : Sale details
C : Sale Paiements It could be that there are sales without details or without paiments. But in a report I need to see all the data. Fe/ Sale 1
ART 2 PAY CASH 10â‚¬ Sale 2
Art 1 Sale 3 <Canceled> Can this be done in a single query or how is this matter solved. Thanks Ralph
Use LEFT JOINs. Assuming there is always data in A, and there may be data in B, and there may be data in C: SELECT A.col1, B.col2, C.col3
LEFT JOIN B ON A.colA = B.colA
LEFT JOIN C ON A.colA = C.colA You’re not telling us how the three tables are related, but use the columns in the foreign key on B that refers to A.
For the foreign key on C, check whether it refers to A or to B, and proceed like for B to A.
Hello back, Thanks for the prompt reply. I explain how the joining is. Table A = TransactionHeaders
PK = HeaderNumber,
Table B = TransactionDetails
PK = DetailNumber,
WSRoot FK = DetailHeader, (link HeaderNumber)
DetailHeaderWS(link WSRoot ) Table C = TransactionPayments
FK = PayHeader, (link HeaderNumber)
PayHeaderWS (link WSRoot ) Already thanks
There is always data in the TransactionHeader, so this goes to the left side of the first LEFT JOIN. There may be data in the TransactionDetails, where the DetailHeader column has an FK reference to the HeaderNumber column on TransactionHeader table. This goes to the right side of the first LEFT JOIN, and the ON clause associates the two columns involved in the FK relationship. For TransactionPayments, the same as for TransactionDetails. Problem #1 here is that the column names on both ends of the FK relationship are different. Especially with a parent/child set of tables, you should use the same column name to avoid confusion. But anyway, here’s the full syntax: SELECT ……..
FROM TransactionHeader A
LEFT JOIN TransactionDetails B ON A.HeaderNumber = B.DetailHeader
LEFT JOIN TransactionPayments C ON A.HeaderNumber = C.PayHeader Once you’re finished with your school assignment, go out and play.
>>Once you’re finished with your school assignment, go out and play.<br /><br />It is alos advisable to play with SQL [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /<a target="_blank" href=http://www.sql-tutorial.net/>http://www.sql-tutorial.net/</a> <br /<a target="_blank" href=http://www.firstsql.com/tutor.htm>http://www.firstsql.com/tutor.htm</a> <br /<a target="_blank" href=http://www.w3schools.com/sql/default.asp>http://www.w3schools.com/sql/default.asp</a><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail