subquery syntax

Question

I have two tables

Table1 Column: ID

100

101

Table2 Columns: ParentID, SectionID and Name

100, 366, Charlie

101, 366, Harry

101, 367, Rose

Table 1 has 2 rows.  I want all 2 in the result of my query regardless

I want to join Table1 to 2 subsets of Table2.  1 Subset is SectionID=366, 2 Subset is SectionID=367 with a result set like this

ID, BoyName,GirlName

100, Charlie, Null

101, Harry, Rose

How can I form a query to give these results?

Answer 1

declare @sno table (id int)
  insert into @sno
  values(100),(101) 
  declare @sno1 table (id int,id2 int,name varchar(20))
  insert into @sno1
   values(100, 366, 'Charlie'),
(101, 366, 'Harry'),
(101, 367, 'Rose')

select C.ID,B.NAme,A.name
from @sno C Left outer JOIN (select ID,Name from @sno1 where ID2= 367) A on A.id=C.ID
left outer JOIN (select ID,Name from @sno1 where ID2= 366) B on B.id=C.ID

---

Hope it Helps!!

Answer 2

Also, try -

DECLARE @tab1 TABLE (id INT)
INSERT INTO @tab1
VALUES(100),(101) 

DECLARE @tab2 TABLE(ParentId INT,SectionId INT,Name VARCHAR(20))
INSERT INTO @tab2
VALUES(100, 366, 'Charlie'),(101, 366, 'Harry'),(101, 367, 'Rose')

SELECT * FROM @tab1
SELECT * FROM @tab2

SELECT b.ParentId
	,MAX(CASE WHEN b.SectionId=366 THEN b.Name END) AS BoyName
	,MAX(CASE WHEN b.SectionId=367 THEN b.Name END) AS GirlName
FROM @tab1 AS a
LEFT OUTER JOIN @tab2 AS b
 ON a.id=b.ParentId
GROUP BY b.ParentId

ParentId	BoyName	GirlName
100	        Charlie	NULL
101	        Harry	Rose

---

Narsimha

Answer 3

That is of course an (the) answer.  Thank You.

Answer 4

I like this way method a little more.  Thank You.