Daniel Pr Daniel Pr - 3 years ago 59
SQL Question

SQL count on a joined table

I've got 2 tables, 1 is

Persons
and 1 is
Relationships
.

I want to retrieve the person with the most children.

Currently I'm joining the tables like this:

SELECT PERSONS.ID, PERSONS.NATIVE_COUNTRY,
RELATIONSHIPS.PERSON_ID, RELATIONSHIPS.RELATION FROM PERSONS
INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID
AND RELATIONSHIPS.RELATION = 'child';


Now I'm trying to count
PERSONS.ID
and get the max, but not sure how to do that.

Answer Source

you can try using group by:

SELECT PERSONS.ID, COUNT(*) FROM PERSONS INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID AND RELATIONSHIPS.RELATION = 'child' GROUP BY persons.id;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download