How to print child, father, mother from these two tables?

user_13595718 0
Viewed 11594

Below are the tables:

relation table

cid pid
101 202
566 322
875 345

people table

id name gender
101 Riya F
566 Aman M
202 Rakesh M
875 lucky M
202 Reena F
322 Raina F
345 Rohit M
322 Mohit M
345 Meena F

output

Child Mother Father
Riya Reena Rakesh
Aman Raina Mohit
Lucky Rohit Meena

I tried this:

SELECT     mother,
           father
FROM      (
                      SELECT     id,
                                 name,
                                 sum(
                                 CASE
                                            WHEN gender = 'F' THEN 1
                                            ELSE 0) AS mother,
                                 sum (
                                 CASE
                                            WHEN gender = 'M' THEN 1
                                            ELSE 0) AS father
                      FROM       people
                      INNER JOIN relation
                      ON         people. id = relation.p_id
                      GROUP BY   id,
                                 name) t1
INNER JOIN relation
ON         relation.p_id = t1.id

Please let me know the query, for how to fetch this output. this above query does not work, I am not able to figure how to output child also.

pivot join group-by oracle sql
7 Answers

You must join relation to 2 copies of people.
The 1st copy will return the child's name and the 2nd copy will return the names of the parents.
Then group by child and use conditional aggregation to get the names of the parents in one row:

SELECT c.name Child, 
       MAX(CASE WHEN p.gender = 'F' THEN p.name END) Mother, 
       MAX(CASE WHEN p.gender = 'M' THEN p.name END) Father
FROM relation r
INNER JOIN people c ON c.id = r.cid
INNER JOIN people p ON p.id = r.pid
GROUP BY r.cid, c.name;

See the demo.

user_104988280

You can join twice and then PIVOT:

SELECT *
FROM   (
  SELECT c.name AS child,
         p.name AS parent,
         p.gender
  FROM   relations r
         INNER JOIN people c
         ON r.cid = c.id
         INNER JOIN people p
        ON r.pid = p.id
)
PIVOT (
  MAX(parent) FOR gender IN (
    'M' AS father,
    'F' AS mother
  )
)

Which, for the sample data:

CREATE TABLE people (id, name, gender) AS
SELECT 101, 'Riya',   'F' FROM DUAL UNION ALL
SELECT 566, 'Aman',   'M' FROM DUAL UNION ALL
SELECT 202, 'Rakesh', 'M' FROM DUAL UNION ALL
SELECT 875, 'lucky',  'M' FROM DUAL UNION ALL
SELECT 202, 'Reena',  'F' FROM DUAL UNION ALL
SELECT 322, 'Raina',  'F' FROM DUAL UNION ALL
SELECT 345, 'Rohit',  'M' FROM DUAL UNION ALL
SELECT 322, 'Mohit',  'M' FROM DUAL UNION ALL
SELECT 345, 'Meena',  'F' FROM DUAL;

CREATE TABLE relations (cid, pid) AS
SELECT 101, 202 FROM DUAL UNION ALL
SELECT 566, 322 FROM DUAL UNION ALL
SELECT 875, 345 FROM DUAL;

Outputs:

CHILD FATHER MOTHER
Riya Rakesh Reena
Aman Mohit Raina
lucky Rohit Meena

db<>fiddle here

user_15092640

Try this one

with cte as
(
 select name as child, r.pid as pid 
   from people p
   join relation r
     on p.id=r.cid
)
select ee.child as child, 
       (select name from people PM where id=ee.pid and PM.gender='F') as Mother,
       (select name from people PP where id=ee.pid and PP.gender='M') as Father 
  from cte ee
user_137286480

You can try below query -

SELECT P.name Child, p2.name mother, p3.name father
  FROM relation R
  JOIN people P ON R.cid = P.id
  JOIN people P2 ON R.pid = P.id
                AND P.gender = 'F'
  JOIN people P3 ON R.pid = P.id
                AND P.gender = 'M'
user_36277560 
SELECT DISTINCT P1.NAME AS CHILD,(SELECT NAME FROM PEOPLE PM WHERE ID=T.PID AND PM.GENDER='F') AS MOTHER,
       (SELECT NAME FROM PEOPLE PP WHERE ID=T.PID AND PP.GENDER='M') AS FATHER FROM 
(SELECT R.CID,NAME,R.PID,P.GENDER FROM PEOPLE P,RELATIONS R
WHERE P.ID=R.PID) T,PEOPLE P1
WHERE T.CID=P1.ID;
user_177740640 
    SELECT l.cid       AS id,
           r.NAME      AS Child,
           mother.NAME AS Mother,
           father.NAME AS Father
    FROM   testdb.dbo.people r
           JOIN testdb.dbo.relation l
             ON l.cid = r.id
           JOIN (SELECT l.cid AS id,
                        r.NAME
                 FROM   testdb.dbo.people r
                        JOIN testdb.dbo.relation l
                          ON l.pid = r.id
                             AND gender = 'F') AS mother
             ON l.cid = mother.id
           JOIN (SELECT l.cid AS id,
                        r.NAME
                 FROM   testdb.dbo.people r
                        JOIN testdb.dbo.relation l
                          ON l.pid = r.id
                             AND gender = 'M') AS father
             ON l.cid = father.id
user_59071990

select * from (select * from (select p.firstname as child, (select p1.firstname from persons p1 where r.p_id=p1.id and p1.gender='M') as father from persons p inner join relations r on p.id=r.id ) t

where t.father is not null ) as table1 inner join

(select * from (select p.firstname as child, (select p1.firstname from persons p1 where r.p_id=p1.id and p1.gender='F') as mother from persons p inner join relations r on p.id=r.id ) t

where t.mother is not null ) as table2

on table1.child=table2.child

user_192399520
Related