To get just personIDs:

SELECT P1.PERSONID
FROM PERSONATTRIBUTE P1 JOIN PERSONATTRIBUTE P2 ON P1.PERSONID=P2.PERSONID
JOIN PERSONATTRIBUTE P3 ON P1.PERSONID=P3.PERSONID
JOIN PERSONATTRIBUTE P4 ON P1.PERSONID=P4.PERSONID
WHERE P1.ATTRIBUTEID=111 AND P2.ATTRIBUTEID=222 AND P3.ATTRIBUTEID=333
AND (P4.ATTRIBUTEID=444 AND NOT EXISTS (SELECT * FROM PERSONATTRIBUTE WHERE PERSONID=P4.PERSONID
AND ATTRIBUTEID=555) OR
P4.ATTRIBUTEID=555 AND NOT EXISTS (SELECT * FROM PERSONATTRIBUTE WHERE PERSONID=P4.PERSONID
AND ATTRIBUTEID=444) )

To get values from Person table:

SELECT P.*
FROM PERSONATTRIBUTE P1 JOIN PERSONATTRIBUTE P2 ON P1.PERSONID=P2.PERSONID
JOIN PERSONATTRIBUTE P3 ON P1.PERSONID=P3.PERSONID
JOIN PERSONATTRIBUTE P4 ON P1.PERSONID=P4.PERSONID
JOIN PERSON P ON P1.PERSONID=P.PERSONID

WHERE P1.ATTRIBUTEID=111 AND P2.ATTRIBUTEID=222 AND P3.ATTRIBUTEID=333
AND (P4.ATTRIBUTEID=444 AND NOT EXISTS (SELECT * FROM PERSONATTRIBUTE WHERE PERSONID=P4.PERSONID
AND ATTRIBUTEID=555) OR
P4.ATTRIBUTEID=555 AND NOT EXISTS (SELECT * FROM PERSONATTRIBUTE WHERE PERSONID=P4.PERSONID
AND ATTRIBUTEID=444) )

----- Original Message -----
From: "tomconlon8421" <tomconlon@...>
To: <firebird-support@yahoogroups.com>
Sent: Saturday, March 13, 2004 3:02 PM
Subject: [firebird-support] Best Method with FB?


> Hi All,
>
> What is the most fastest way with IB/IBO to do the following:
>
> 1. Person table (80k rows) keyfld: PersonID
> 2. Attribute Table (5k rows) keyfld: AttributeID
> 3. PersonAttribute table with 2.1 million rows
> (PersonID,AttributeID,Level )
>
> Problem:
> Return quickly all persons that have attributeids
> (111,222,333,444,555).
> However they _must have_ 111,222,333 and _one of_ 444 or 555.
>
> This is not as simple as it may seem to some people and also this is
> the simplest case, so any ideas for efficiency would be good.
>
> Possibilities:
> 1. Clever use of GROUP BY?
> 2. Loading a small table then using a sp for 111,222,333
> sp 'cursor' to check 444,555
> 3. UNION ALL seems to work but UNION is limited in FB
> 4. Extract 111,222,333 then use client-side to process? (IBO)
>
> Is there anyway to use GROUP BY PersonID HAVING COUNT(*)>3 and testing
> somehow that 111,222,333 are present? Or other ideas, self-join?
> Frankly I keep well away from sub-selects in IB as they go off with
> the fairies.
>
>
> TIA,
> Tom
>
> ps( In reality the resultset needs to be joined into the Person table
> to retrieve more columns. As a side point the users have the ability
> to currently specify 'level' also which makes it even more complex.)