Обсуждение: request help forming query
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. Can anyone help with this please? Thanks!
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
Given that (type_id, group_id) is the PK of widgets it is possible to avoid self-join: select group_id from widgets where type_id = A or type_id = B group by group_id having count(1) = 2;
yes, this could get called on quite large tables (maybe not billions ...). The second solution looks useful - I'll try it on some test data. thanks both of you.