Re: SQL question: checking all required items

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