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

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

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
Updated 28-Aug-14 21:09pm

SELECT p.Pid, Name, Address, Child1Id, Amount, Child2Id, Expense 
    (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:
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.
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

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

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

Check this...

SELECT,,a.address,b.Child1Id,b.amount,null as 'Child2Id', null as 'Expense' from parent a
 left outer join child1 b on
SELECT,,a.address,null as 'Child1Id', null as 'amount',b.Child2Id,b.Expense from parent a
left outer join child2 b on

Hope this will give you expected output.


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