15,169,249 members
See more:
 Parent table Pid Name Address 1 Ragu xyz 2 Rages 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 1 100 null null 1 Ragu xyz 1 200 null null 1 Ragu xyz null null 1 1000
Posted
Updated 28-Aug-14 21:09pm
v3

## Solution 1

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
PhilLenoir 28-Aug-14 12:02pm

Thanks Nelek, I keep forgetting to use the code formatting option!
FeroseKhan 29-Aug-14 2:15am

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!

## Solution 2

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

## Solution 3

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