SQL Query using Outer join [Resolved]

Posted by Santosh4u under Sql Server on 5/3/2016 | Points: 10 | Views : 244 | Status : [Member] | Replies : 1
Hi, i am trying to use left outer and right outer join with mail table but i can see some of the record are missing.

i have 3 tables , table A, Table B, Table C.

TableA(Details Table)
Id,Name, BID(TableB) CID(TableC) Date
1, Test1 , 2 , 1 , 12-01-2016
2 , Test2 , 3 ,3 , 13-01-2016
3 , Test 3 , 3 ,3 , 14-01-2016

TableB(Master Table)
BID Desc
1 , Desc B 1
2 , Desc B2
3 , Desc B3

Table C(Master Table)
CID Desc
1, Desc C1
2 , Desc C2
3, Desc C3
4 , Desc C4
5 , Desc C5

i want get all records from table A and matching record from tableB
with all the record from tableC and matching record from table A

i am using TableA left outer join table B right outer join tableC but some of record are missing from tableC.

please some one help me on this.

Thanks







now i want to get all the records from tableA




Responses

Posted by: Professionaluser on: 5/5/2016 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
DECLARE @TableA table( Id int,Name varchar(100), BID int,  CID int, Date datetime)
insert @TableA
SELECT 1, 'Test1' , 2 , 1 , '12-01-2016' union all
SELECT 2 , 'Test2' , 3 ,3 , '13-01-2016' union all
SELECT 3 , 'Test 3' , 3 ,3 , '14-01-2016'

DECLARE @TableB table(BID int, Descr varchar(100))
insert @TableB
SELECT 1 , 'Desc B 1' union all
SELECT 2 , 'Desc B2' union all
SELECT 3 , 'Desc B3'

declare @TableC table (CID int, Descr varchar(100))
insert @TableC
SELECT 1, 'Desc C1' union all
SELECT 2 , 'Desc C2' union all
SELECT 3, 'Desc C3' union all
SELECT 4 , 'Desc C4' union all
SELECT 5 , 'Desc C5'

--i want get all records from table A and matching record from tableB with all the record from tableC and matching record from table A
SELECT *
FROM @TableA A
LEFT JOIN @TableB B ON A.BID = B.BID
RIGHT JOIN @TableC C ON A.CID = C.CID

/*
result set
1 Test1 2 1 2016-01-12 00:00:00.000 2 Desc B2 1 Desc C1
NULL NULL NULL NULL NULL NULL NULL 2 Desc C2
2 Test2 3 3 2016-01-13 00:00:00.000 3 Desc B3 3 Desc C3
3 Test 3 3 3 2016-01-14 00:00:00.000 3 Desc B3 3 Desc C3
NULL NULL NULL NULL NULL NULL NULL 4 Desc C4
NULL NULL NULL NULL NULL NULL NULL 5 Desc C5
*/
Here i could see all records from TableC...


Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response