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 2005 Forums
 Transact-SQL (2005)
 Help~ SQL Query Again

Author  Topic 

tiffiny
Starting Member

7 Posts

Posted - 2007-08-09 : 10:52:51
TableA
ID Name
1 Test1
2 Test2
3 Test3

TableB
ID Item
1 Item1
2 Item2

TableLink - AID=tableA.ID, BID=tableB.ID
ID AID BID
1 1 1

How to produce the result like below without using UNION????
ID Name Item
1 Test1 <NULL>
1 Test1 Item1
2 Test2 <NULL>
3 Test3 <NULL>

I try on the query below:
SELECT TableA.ID, TableA.Name, TableB.Item
FROM TableB INNER JOIN
TableC ON TableB.ID = TableC.BID RIGHT OUTER JOIN
TableA ON TableC.AID = TableA.ID
But the result is
ID Name Item
1 Test1 Item1
2 Test2 <NULL>
3 Test3 <NULL>


jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-09 : 12:08:28
You should read up about the different types of JOINS, this is the same question you asked earlier and your instructor might make you explain in writing the differences between them.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-09 : 13:50:36
Here are two ways that work with your sample data. Also, I think it'll continue to work if you add mroe data:
DECLARE @TableA TABLE (ID INT, [Name] VARCHAR(10))

INSERT @TableA
SELECT 1, 'Test1'
UNION ALL SELECT 2, 'Test2'
UNION ALL SELECT 3, 'Test3'

DECLARE @TableB TABLE (ID INT, Item VARCHAR(10))

INSERT @TableB
SELECT 1, 'Item1'
UNION ALL SELECT 2, 'Item2'

DECLARE @TableC TABLE (ID INT, AID INT, BID INT)

INSERT @TableC SELECT 1, 1, 1

-- Option 1 (*should* be more effecient)
SELECT DISTINCT
A.ID,
A.Name,
CASE
WHEN C.BID IS NOT NULL THEN B.Item
ELSE NULL
END AS Item
FROM
@TableA AS A
CROSS JOIN
@TableB AS B
LEFT OUTER JOIN
@TableC AS C
ON A.ID = C.AID
AND B.ID = C.BID
LEFT OUTER JOIN
@TableB AS B1
ON C.BID = B1.ID

-- Option 2 with Derived Table
SELECT
T.ID,
T.Name,
B.Item
FROM
@TableB AS B
RIGHT OUTER JOIN
(
SELECT DISTINCT
A.ID,
A.Name,
C.BID
FROM
@TableA AS A
CROSS JOIN
@TableB AS B
LEFT OUTER JOIN
@TableC AS C
ON A.ID = C.AID
AND B.ID = C.BID
) AS T
ON B.ID = T.BID



-Ryan
Go to Top of Page

tiffiny
Starting Member

7 Posts

Posted - 2007-08-11 : 07:34:41
thanks!!!!
Go to Top of Page
   

- Advertisement -