Thread: SQL question: checking all required items

SQL question: checking all required items

From
Raymond O'Donnell
Date:
Hi all,

Given the following tables -

   create table people (
     person_id text primary key,
     person_name text,
     [...etc...]
   );

   create table items (
     item_id text primary key,
     item_name text,
     is_required boolean,
     [...etc...]
   );

   create table items_for_people (
     person_id text,
     item_id text,
     primary key (person_id, item_id),
     foreign key person_id references people(person_id),
     foreign key item_id references items(item_id)
   );


- how can I find those people who don't have _all_ of the items which
are marked "required"?

In other words, how do I select those rows in "people" which don't have
a corresponding row in "items_for_people" for *each* row in "items"
which has is_required=true?

Many thanks,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL question: checking all required items

From
"Scott Marlowe"
Date:
On 8/10/07, Raymond O'Donnell <rod@iol.ie> wrote:
> - how can I find those people who don't have _all_ of the items which
> are marked "required"?
>
> In other words, how do I select those rows in "people" which don't have
> a corresponding row in "items_for_people" for *each* row in "items"
> which has is_required=true?

Without writing the exact query you need, I'll give you a couple of
ways to solve the problem of finding things in one set that aren't in
another.

select table1.id from table1 left join table2 on (table1.id=table2.id)
where table2.id is null
OR
select table1.id from table1 where table1.id is not in (select id from table2);

Re: SQL question: checking all required items

From
Raymond O'Donnell
Date:
On 10/08/2007 21:29, Scott Marlowe wrote:


> select table1.id from table1 where table1.id is not in (select id from table2);

Duh! I should have thought of that.... thanks for that, and apologies
for the stupidity (blame it on the glass of wine I had with dinner!).

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL question: checking all required items

From
"Scott Marlowe"
Date:
On 8/10/07, Raymond O'Donnell <rod@iol.ie> wrote:
> On 10/08/2007 21:29, Scott Marlowe wrote:
>
>
> > select table1.id from table1 where table1.id is not in (select id from table2);
>
> Duh! I should have thought of that.... thanks for that, and apologies
> for the stupidity (blame it on the glass of wine I had with dinner!).

It's only obvious after you've done it a few times...

Show us the query when you're done, I'm sure there are enough folks
who'd like to see your solution.

Re: SQL question: checking all required items

From
Raymond O'Donnell
Date:
On 10/08/2007 22:03, Carlos Ortíz wrote:

> Select * from people where person_id in (
>       Select person_ID from Items_for_people group by Person_id Having Count(*) = (
>            Select count(*) from Items Where is_required = true))

That seems to work fine! I'd only change "having count(*) = ..." to
"having count(*) >= ..." to allow for people having other items in
addition to the required ones.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL question: checking all required items

From
Raymond O'Donnell
Date:
On 10/08/2007 21:42, Scott Marlowe wrote:

> Show us the query when you're done, I'm sure there are enough folks
> who'd like to see your solution.

Here's what I came up with:

   select distinct ip.person_id from items_for_people ip
   where exists (
     (
       select item_id from items
       where is_required = true
     )
     except
     (
       select ip2.item_id from items_for_people ip2
       inner join items i on (ip2.item_id = i.item_id)
       where ip2.person_id = ip.person_id
       and i.is_required = true
     )
   )

This finds all those who don't have all the required items, whatever
else they may have.

Comments and improvements are welcome!

Thanks for the help,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL question: checking all required items

From
Carlos Ortíz
Date:
?
Try
 
Select * from people where person_id in (
      Select person_ID from Items_for_people group by Person_id Having Count(*) = (
           Select count(*) from Items Where is_required = true))
 
Or something like that. That's the idea. Probe it and tell us.
(May be the sintaxis it's not correct, but I'm new in postgresql. In sql server it's ok)
 


De: pgsql-general-owner@postgresql.org en nombre de Raymond O'Donnell
Enviado el: Vie 10/08/2007 03:07 p.m.
Para: 'PostgreSQL'
Asunto: [GENERAL] SQL question: checking all required items

Hi all,

Given the following tables -

   create table people (
     person_id text primary key,
     person_name text,
     [...etc...]
   );

   create table items (
     item_id text primary key,
     item_name text,
     is_required boolean,
     [...etc...]
   );

   create table items_for_people (
     person_id text,
     item_id text,
     primary key (person_id, item_id),
     foreign key person_id references people(person_id),
     foreign key item_id references items(item_id)
   );


- how can I find those people who don't have _all_ of the items which
are marked "required"?

In other words, how do I select those rows in "people" which don't have
a corresponding row in "items_for_people" for *each* row in "items"
which has is_required=true?

Many thanks,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

---------------------------(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