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?

il_guru · Answer 1 · 2012-11-12T14:02:59.740

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

Thanks. I think I got confused with 'group by'. BTW is it possible to avoid the 'in' subquery? The people tabel has around 2 million rows and this query might be too slow :( — Sudar, Nov 12 '12 at 13:48

score 2 · Accepted Answer · answered Nov 12 '12 at 14:01

Try:

select p.name, t2.type
from types t1
join mapping m1 on m1.guid = t1.guid
join people p on p.wpid = m1.wpid 
join mapping m2 on p.wpid = m2.wpid 
join types t2 on m2.guid = t2.guid
where t1.type = 'politician'
order by 1, 2

- for a list of all politicans and all the types that they belong to.

Alternatively, if you just want a list of all politicians and the number of different types that they belong to, try:

select p.name, count(*)
from mapping m1
join people p on p.wpid = m1.wpid 
join mapping m2 on p.wpid = m2.wpid 
where m1.guid = 1 /* replace 1 with appropriate guid for politicians */
group by p.name
order by 1

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

2 Answers2