Re: restrictive WHERE-IN

<posted and mailed>
Hi, Joerg.

I don't have the chance to set up a table like yours now and try this, but I think this will work. As I understand it, the INTERSECT function will look for matches in two or more select statements, like this:

SELECT person_id
FROM table
WHERE value_id = 1
INTERSECT
SELECT person_id
FROM table
WHERE value_id = 2
INTERSECT
SELECT person_id
FROM table
WHERE value_id = 3
INTERSECT
SELECT person_id
FROM table
WHERE value_id = 4
ORDER BY person_id
/

should work. Maybe someone else will deliver a better way. I tested this (as near as I could) with some tables and data that I *do* have, and it seems to work.

Chris

Joerg Leute wrote:

> Hi everybody
>
> Is there any way to have a restrictive where-in query?
>
> Table1:
> person_id value_id
> 1 1
> 1 2
> 1 3
> 1 4
> 2 1
>
> If i now
> SELECT * FROM PERSON WHERE VALUE_ID IN (1,2,3,4)
> i get person 1 and 2
>
> is there any way to return person 1 only, because person 1 has every value.
>
> Thanks in advance
>
> Joerg
Received on Sun Jun 27 1999 - 22:03:38 CDT