CODEWITHSUNDEEP
sqloracle-database
Higeath
Higeath

Reputation: 3

Pulling a data from a inner join table and merging two rows into one

What I want to achieve from query is select all avatars and their father and mother (by getting parentID and gender to determine) and display for each avatar 1 row with avatar username father username mother username.

This is kind of a solution I was thinking about not finished a subquery that returns first father and another subquery for inner join to return mother

SELECT avatars.username as ChildUsername, father.username as FatherUsername, mother.username as MotherUsername
FROM avatars
INNER JOIN avatars father(
  SELECT *
  FROM children
  INNER JOIN children child ON 
  WHERE avatars.gender = 'M' AND 
)
INNER JOIN avatars mother

Database:

CREATE TABLE avatars
    (avatarID NUMBER CONSTRAINT pk_avatars PRIMARY KEY,
    username VARCHAR2(20),
    gender CHAR(1)
    );

CREATE TABLE children
    (childID NUMBER CONSTRAINT pk_children PRIMARY KEY,
    childAvatarID NUMBER,
    parentAvatarID NUMBER
    );

ALTER TABLE children
ADD FOREIGN KEY (parentAvatarID)
REFERENCES avatars(avatarID);

ALTER TABLE children
ADD FOREIGN KEY (childAvatarID)
REFERENCES avatars(avatarID);

Data:

INSERT INTO avatars VALUES (1,'av1','M');
INSERT INTO avatars VALUES (2,'av2','F');
INSERT INTO avatars VALUES (3,'av3','M');

INSERT INTO children VALUES (1,3,1); //Father 
INSERT INTO children VALUES (2,3,2); //Mother

for the above data I would want to get this output

   ChildUsername FatherUsername MotherUsername
   av3           av1            av2

Upvotes: 0

Views: 40

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You'll need to join the avatars table twice - self and parent.

select s.username as ChildUsername,
    max(case when p.gender = 'M' then p.username end) as FatherUsername,
    max(case when p.gender = 'F' then p.username end) as MotherUsername,
    max(case when p.gender = 'M' then p.someothercolumn end) as FathersSomeOtherColumn,
    max(case when p.gender = 'F' then p.someothercolumn end) as MothersSomeOtherColumn,
    . . .
from children c
join avatars s on c.childAvatarID = s.avatarId
left join avatars p on c.parentAvatarId = p.avatarId
group by c.childAvatarID,
    s.username;

Upvotes: 2

SqlZim
SqlZim

Reputation: 38023

Using conditional aggregation:

select 
    av.username
  , max(case when p.gender = 'M' then p.username end) as Father
  , max(case when p.gender = 'F' then p.username end) as Mother
from avatars av
  inner join children c
    on av.avatarid = c.childavatarid
  left join avatars p
    on p.avatarid = c.parentavatarid
group by av.username

Upvotes: 1

Jeremy Real
Jeremy Real

Reputation: 766

I believe this would work to select the mothers and fathers as subqueries.

SELECT distinct a.username as ChildUsername, f.FatherUsername, m.MotherUsername
FROM avatars a
INNER JOIN (
  SELECT a.username as FatherUsername, c.childAvatarID
  FROM avatars a
  INNER JOIN children c 
  ON a.avatarID = c.parentAvatarID
  WHERE a.gender = 'M'
) f
ON a.avatarID = f.childAvatarID
INNER JOIN (
  SELECT a.username as MotherUsername, c.childAvatarID
  FROM avatars a
  INNER JOIN children c 
  ON a.avatarID = c.parentAvatarID
  WHERE a.gender = 'F'
) m
ON a.avatarID = m.childAvatarID

Upvotes: 0

Related Questions