SQL count on a joined table

Question

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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;

SQL count on a joined table

Sign up