CODEWITHSUNDEEP
sqloracle-database
Higeath
Higeath

Reputation: 13

Counting inner joined query

I have two tables one with 'Avatars' and one with 'Children' the children table holds values of parent avatar ID and of child avatar ID. I'm trying to get from the table a list of avatars that have more than 2 children.

Tables:

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,
    mainParent NUMBER(1,0)
    );

Relationships:

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

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

My query:

SELECT count(children.avatarid), avatars.username
FROM avatars
INNER JOIN (
  SELECT *
  FROM avatars
  INNER JOIN children ON avatars.avatarID = children.childAvatarID
  WHERE avatars.gender = 'M'
) children ON avatars.avatarID = children.parentavatarID
WHERE avatars.gender = 'M'
GROUP By children.avatarid, avatars.username;

It shows for count(children.avatarid) always 1 no matter how many children an avatar has.

Upvotes: 1

Views: 39

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

list of avatars that have more than 2 children

Try this:

select a.*,
    c.child_count
from avatars a
join (
    select parentAvatarId,
        count(*) as child_count
    from children c
    join avatars a on a.avatarId = c.childAvatarId
    where a.gender = 'M'
    group by parentAvatarId
    having count(*) > 2
    ) c on a.avatarId = c.parentAvatarId
where a.gender = 'M'

Upvotes: 1

Uncle Iroh
Uncle Iroh

Reputation: 6055

select avatars.avatarID, count(children.childID) as numberOfChildren
from avatars 
join children on children.parentAvatarID = avatars.avatarID
group by children.parentAvatarID
having numberOfChildren >= 2;

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

You are grouping by children.avatarid, avatars.username, try just grouping by avatars.username.

SELECT count(children.avatarid), avatars.username
FROM avatars
INNER JOIN (
  SELECT *
  FROM avatars
  INNER JOIN children ON avatars.avatarID = children.childAvatarID
  WHERE avatars.gender = 'M'
) children ON avatars.avatarID = children.parentavatarID
WHERE avatars.gender = 'M'
GROUP By avatars.username;

Upvotes: 2

Related Questions