Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql joins Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 1:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:18 AM
Points: 53, Visits: 220
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....


Post #1465366
Posted Wednesday, June 19, 2013 2:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 17, 2014 7:18 AM
Points: 2,439, Visits: 6,426
Just OUTER JOIN tableA, tableB and tableC on id_number.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1465371
Posted Wednesday, June 19, 2013 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 11,965, Visits: 10,992
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465385
Posted Thursday, June 20, 2013 5:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:18 AM
Points: 53, Visits: 220
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.

Post #1465593
Posted Thursday, June 20, 2013 6:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
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




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse