Re: SQL question: checking all required items

[Carlos Ortíz <carlos.ortiz@xxxxxxxxxxx>]

Title: SQL question: checking all required items

?

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@xxxxxxxxxxxxxx en nombre de Raymond O'Donnell
Enviado el: Vie 10/08/2007 03:07 p.m.
Para: 'PostgreSQL'
Asunto: 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@xxxxxx
---------------------------------------------------------------

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