Thread: 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.