Select query with group by clause and multiple inner joins in Postresql

Question

I have 3 tabels with the following definitions

people
------
- wid 
- name

types
-----
- guid
- type

mapping
-------
- guid
- wid

The people table has the list of people

The types table the type information for each row present in the people table. If a person belongs to more than one type, then two rows are present in the types table.

The mapping table provides a mapping between people and types table.

Now to find out who are the people of type 'politician' I can use the following query.

select name from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician'

But now I want to find out what are the other types a politician belongs. I know that I have to use group by and having clause. But I am not able to come up with the query. How to write this query?

Answer 1

A group by has to be used to give the result of an aggregate function on a group of value (like receiving the count of different types, or a sum of values). If you just need to get what group of types a people belongs too, you can use a single query like this.

select name, types
from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid
where people.wpid in (select people.wpid from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician')

A group by would be useful to know how many groups a politician is into

select name, count(types)
from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid
where people.wpid in (select people.wpid from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician')
group by name

EDIT: avoid IN subquery

If you know the guid of the politician group, you could do something like this. I did not test the query, but the idea is to filter the people table using a join with the mapping table with guid equal to the politician guid

select p.name, count(t.types)
from people p inner join mapping m1
on p.wid = m1.wid and m1.guid = [politician guid]
inner join mapping m2
on p.wid = m2.wid
inner join types t
in m2.guid = t.guid