danmcb wrote:
Hi !'ve been wondering how to formulate a query to get a set of objects out of a database, and am a bit stuck. I hope that someone here might be able to help. This is what the db looks like: Table TYPES id int primary key, description text Table GROUPS id int primary key description text Tables WIDGETS type_id int references TYPES(id), group_id int references GROUPS(id), primary key(type_id, group_id) Now, given two type_id's, say A and B, I would like to find all groups (or group_id's of course) that have a widget of both of these two types.
There must be a more a elegant method but here's the first thing that came to me:
SELECT group_id FROM widgets WHERE type_id = $1 AND group_id IN ( SELECT DISTINCT group_id FROM widgets WHERE type_id = $2 ); I trust you aren't planning to run this on billions of rows ... b ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match