Simple T-SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Simple T-SQL Query

Author

Topic

tlbaxter
Starting Member

2 Posts

Posted - 2009-12-22 : 16:37:51

Hello,

I'm using SQL Server 2008.

I have a table of parts (call it "Parts"). This table has, among other things, an "Id" column. I have two other tables (call them "A" and "B") that contain foreign keys into Parts. Not every row in "Parts" has a corresponding row in tables "A" or "B". The Id columns in "A" and "B" are disjoint (i.e., they do not overlap).

Here's some sample data (I only show the Id column):
Parts
=====
23
24
25
26
27
28

A
=
24

B
=
26
28

What I'd like is a query that returns every row in "Parts" as well as a column that contains either "A" (if there is a corresponding row in "A"), "B" (if there is a corresponding row in "B"), or null (if there is no corresponding row in "A" or "B".

Here is the sample output I'm looking for:
---------------
23 null
24 A
25 null
26 B
27 null
28 B

How can I write such a query?

Thanks

--
Tom Baxter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-22 : 16:41:43

SELECT p.ID, CASE WHEN a.ID IS NOT NULL THEN 'A' WHEN b.ID IS NOT NULL THEN 'B' ELSE NULL END AS Col2
FROM Parts AS p
LEFT JOIN TableA AS a ON a.ID = p.ID
LEFT JOIN TableB AS b ON b.ID = p.ID

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-22 : 16:43:02

SELECT p.ID, x.TableName
FROM Parts AS p
LEFT JOIN (SELECT 'A' AS TableName, ID FROM TableA UNION ALL SELECT 'B', ID FROM TableB) AS x ON x.ID = p.ID

N 56°04'39.26"
E 12°55'05.63"

tlbaxter
Starting Member

2 Posts

Posted - 2009-12-22 : 16:58:31

quote:
Originally posted by Peso

SELECT p.ID, CASE WHEN a.ID IS NOT NULL THEN 'A' WHEN b.ID IS NOT NULL THEN 'B' ELSE NULL END AS Col2
FROM Parts AS p
LEFT JOIN TableA AS a ON a.ID = p.ID
LEFT JOIN TableB AS b ON b.ID = p.ID

N 56°04'39.26"
E 12°55'05.63"

That's perfect!! It worked like a charm. Thank you so much!!!

--
Tom Baxter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources