June 19, 2013 at 1:58 pm
I have three tables.
TableA
id_number
date
name
TableB
id_number
notes
status
TableC
id_number
location
item
Sometimes data may be entered in TableA and TableB.
Sometimes data may be entered in TableA and TableC.
Sometimes data may be entered in TableA and TableB and Table C..
I would like to return:
A.id_number B.notes B.status C.location C.item
..leaving fields from B or C empty when there is no data from them.
So, I have:
SELECT A.id_number B.notes B.status
FROM TableA as A
INNER JOIN TableB as B
ON A.id_number = B.id_number
...and I have
SELECT A.id_number C.location C.item
FROM TableA as A
INNER JOIN TableC as C
ON A.id_number = C.id_number
...how do I then combine the two so that I get
A.id_number B.notes B.status C.location C.item
Thanks....
June 19, 2013 at 2:05 pm
Just OUTER JOIN tableA, tableB and tableC on id_number.
June 19, 2013 at 2:33 pm
Like this:
SELECT A.id_number, B.notes, B.status, C.location, C.item
FROM TableA as A
LEFT JOIN TableB as B ON A.id_number = B.id_number
LEFT JOIN TableC as C ON A.id_number = C.id_number
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 5:20 am
Jan Van der Eecken (6/19/2013)
Just OUTER JOIN tableA, tableB and tableC on id_number.
Point of clarification -- sometimes data will be entered in TableA and not in TableB or TableC. We do not want the rows from TableA unless there is matching data in TableB and/or TableC.
My understanding is that an outer join would return all rows from TableA.
June 20, 2013 at 6:18 am
inevercheckthis2002 (6/20/2013)
Jan Van der Eecken (6/19/2013)
Just OUTER JOIN tableA, tableB and tableC on id_number.Point of clarification -- sometimes data will be entered in TableA and not in TableB or TableC. We do not want the rows from TableA unless there is matching data in TableB and/or TableC.
My understanding is that an outer join would return all rows from TableA.
Quite simple. Add a WHERE clause to the query provided by Sean.
SELECT A.id_number, B.notes, B.status, C.location, C.item
FROM TableA as A
LEFT JOIN TableB as B ON A.id_number = B.id_number
LEFT JOIN TableC as C ON A.id_number = C.id_number
WHERE B.is_number IS NOT NULL OR C.id_number IS NOT NULL
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply