SQL Query using Outer join [Resolved]

Posted by Santosh4u under Sql Server on 5/3/2016 | Points: 10 | Views : 1092 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts