[SQL] Searching for results with an unknown amount of data

[DownLoad X]

Hi,

I've got two tables, A and B, the first one containing columns

A_ID | info

where A_ID is primary, so that this table stores various information about 
an object, and the second containing columns


A_ID | property

where property is an integer referring to a particular property that an 
object may possess.  I've seperated these into two tables so that an object 
may have several (ie an unknown number of) properties.


Now, I want to find all objects that have at most properties 1,2,3, say (so 
something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this 
-- can anyone help?
Also, what if I want to find all the objects possessing properties 4,5,6, as 
well as possibly other things? I've done it with nested SELECTs (constructed 
programmatically), but I think this is quite slow and not a very pleasing 
solution.


Obviously, both these things will need to be done for an arbitrary list of 
integers.


Thanks,
DL



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Searching for results with an unknown amount of data

[DownLoad X]

> Now, I want to find all objects that have at most properties 1,2,3, say 
(so
> something with (1,2) is okay, as is (1,2,3)). I can't see a way to do 
this

> -- can anyone help?

It sounds like you are trying to find all objects that do not have any
properties outside of a specific list. One way to get that list is:


That's exactly right.



SELECT a_id
  FROM a
WHERE
  NOT EXISTS
(SELECT 1
  FROM b
  WHERE
b.a_id = a.a_id
AND
b.property NOT IN (1, 2, 3)
)
;


Yupp, this appears to do it! The 'double negation' is very clever.

Thanks a lot,
DL



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq