Need a sql query for the following output in sql server 2008 using join

Question

1.00/5 (2 votes)

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 28-Aug-14 5:39am

FeroseKhan

Updated 28-Aug-14 20:09pm

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

PhilLenoir · Answer 1 · 2014-08-28T06:00:00

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.

Prasanth Radhakrishanan · Answer 2 · 2014-08-28T21:01:00

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

Magic Wonder · Answer 3 · 2014-09-02T04:00:00

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

Need a sql query for the following output in sql server 2008 using join

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0