CODEWITHSUNDEEP
sqlsql-server
RCY
RCY

Reputation: 33

Two inner join with Condition not working

Tbl_family

#ID Name    Gender
1   Ram M         
2   Sasank  M         
3   Maya    F         
4   Manu    F         
5   Anu F         
6   Raj M

tbl_childparent

#Child_id   Parent_id
1   2
1   3
4   5
4   6

I want a output which needs to display ChildID,ChildName,Father,Mother This is my sql query but it doesn't work

SELECT tf2.ID,tf2.Name AS ChildName,tf.Name AS Father,tf3.Name AS Mother 
FROM dbo.tbl_clildparent tc
INNER JOIN dbo.tbl_Family tf ON tc.Parent_id=tf.ID 
    AND tf.Gender='M'
INNER JOIN dbo.tbl_Family tf3 ON tc.Parent_id = tf3.ID 
    AND tf3.Gender='F'
LEFT OUTER JOIN dbo.tbl_Family tf2 ON tc.Child_id = tf2.ID

Upvotes: 1

Views: 530

Answers (4)

Mahi
Mahi

Reputation: 1

with cte as
(
select cp.Child_ID, c.Name as 'ChildName' 
cp.Parent_ID, p.Name as 'ParentName', p.Gender as 'ParentGender'
from tbl_childparent cp
join Tbl_family p on cp.Parent_ID = p.ID
join Tbl_family c on cp.Child_ID = c.ID
)

select t1.CHildID, t1.ChildName, t1.ParentName as Father, t2.ParentName as Mother from cte t1
join cte t2 on t1.child_id = t2.child_ID
where t1.ParentGender = M and t2.ParentGender = 'F'

Upvotes: 0

udhaya kumar
udhaya kumar

Reputation: 169

SELECT Id,ChildName,
MAX(CASE WHEN Gender='M' THEN Father END)Father,
MAX(CASE WHEN Gender='F' THEN Mother END)Mother 
FROM (
SELECT tf2.ID,tf2.Name AS ChildName,tf.Name AS Father,tf.Name AS Mother,tf.Gender
FROM #TEMP1 tc
INNER JOIN #TEMP tf ON tc.Parentid=tf.ID 
LEFT JOIN #TEMP tf2 ON tc.Childid = tf2.ID
)A GROUP BY Id,ChildName

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: you can achieve desired output using CASE and GROUP BY as below:

select tf.id AS ChildID, tf.Ename AS ChildName, 
    Max(case when tfp.gender = 'm' then tfp.Ename end) Father, 
    MAX(case when tfp.gender = 'f' then tfp.Ename end) Mother
from #tbl_childparent tc
left join #tbl_Family tf on tc.child_id = tf.id
left join #tbl_Family tfp on tc.parent_id = tfp.id
group by tf.id, tf.Ename

OUTPUT:

ChildID ChildName   Father  Mother
4       Manu        Raj     Anu 
1       Ram         Sasank  Maya

Upvotes: 2

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Try the below query

SELECT  Child_id,N.Name,
        MAX((CASE WHEN F.Gender = 'M' THEN F.Name ELSE NULL END )) AS Father,
        MAX((CASE WHEN M.Gender = 'F' THEN M.Name ELSE NULL END )) AS Mother
FROM    @tbl_childparent    AS  C
    INNER JOIN  @Tbl_family AS  N   ON  N.ID    =   C.Child_id
    LEFT JOIN @Tbl_family   AS  F   ON  F.ID    =   C.Parent_id
    LEFT JOIN @Tbl_family   AS  M   ON  M.ID    =   C.Parent_id
GROUP BY Child_id,N.Name

OUTPUT:

Child_id    Name    Father  Mother
1           Ram     Sasank  Maya
4           Manu    Raj     Anu

Upvotes: 1

Related Questions