Click here to Skip to main content
15,169,249 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Parent table
PidName  Address
1Ragu xyz
2Rages yyy


Child1
Child1Id Pid Amount
1 1 100
2 1 200
3 2 300



Child2
Child2Id Pid Expense
1 1 1000


Ouput should be like this

Pid Name Address Child1Id Amount Child2Id Expense
1 Ragu xyz 1100 null null
1 Ragu xyz 1 200 null null
1 Ragu xyz null
null 1 1000
Posted
Updated 28-Aug-14 21:09pm
v3

SQL
SELECT p.Pid, Name, Address, Child1Id, Amount, Child2Id, Expense 
FROM 
    (SELECT [Parent table].Pid, Name, Address, Child1Id, Amount
     FROM [Parent table] LEFT OUTER JOIN Child1 ON [Parent table].Pid = Child1.Pid) p
          LEFT OUTER JOIN Child2 ON p.Pid = Child2.Pid
Notes: I've assumed that by "A" you mean "Ragu" in your example and that you were only showing part of the example. If you want ouput filtered to just Pid = 1 then add:
SQL
WHERE p.Pid = 1


On reflection, I thought I'd add some words of caution:
This type of query can introduce a "cartesian product" (see this article[^] for a full explanation).

Cartesian products can cause confusion and will lead to wrong answers if you carry out aggregation on your dataset. A typical solution for aggregation is to carry out your aggreagation in your nested sub-select.
   
v4
Comments
PhilLenoir 28-Aug-14 12:02pm
   
Thanks Nelek, I keep forgetting to use the code formatting option!
FeroseKhan 29-Aug-14 2:15am
   
Thanks for your reply .
Your Query not retrieving the exact output.
It returns the output based on child1 table (i.e) it will return only first 2 records as per the sample
PhilLenoir 2-Sep-14 9:25am
   
Sorry, there was a typo, the joins should have been "LEFT OUTER" not "LEFT INNER"; there's no such thing as a "LEFT INNER" join!

try this



SQL
select p.pid,p.name,p.address ,c1.childid,c1.amount,c2.child2id,c2.expence from paraent p left outer join child c1 on p.pid=c1.childid left outer join child2 c2 on p.pid=c2.child2id where c1.childid=p.pid  union all

select p.pid,p.name,p.address ,c1.childid,c1.amount ,c2.child2id,c2.expence from paraent p full join child2 c2 on p.pid=c2.child2id left outer join child c1 on p.pid=c1.childid   where c2.child2id= p.pid
   
v2
Hi,

Check this...


SQL
SELECT a.pid,a.name,a.address,b.Child1Id,b.amount,null as 'Child2Id', null as 'Expense' from parent a
 left outer join child1 b on a.pid=b.pid
WHERE a.pid=1
union
SELECT a.pid,a.name,a.address,null as 'Child1Id', null as 'amount',b.Child2Id,b.Expense from parent a
left outer join child2 b on a.pid=b.pid
WHERE a.pid=1



Hope this will give you expected output.

Cheers
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900