From: | Carlos Ortíz <carlos(dot)ortiz(at)stps(dot)gob(dot)mx> |
---|---|
To: | "Raymond O'Donnell" <rod(at)iol(dot)ie>, "PostgreSQL" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SQL question: checking all required items |
Date: | 2007-08-10 21:03:48 |
Message-ID: | 17672AB7CE8E5649A043EF56118A63DC3D865B@CCEXGCLUS.stps.gob.mx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
?
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(at)postgresql(dot)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(at)iol(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-10 21:38:08 | Re: CREATE RULE on VIEW with INSERT after UPDATE does not work |
Previous Message | Tom Lane | 2007-08-10 20:59:52 | Re: timestamp skew during 7.4 -> 8.2 upgrade |