Subject Best Method with FB?
Author tomconlon8421
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 )

Return quickly all persons that have attributeids
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.

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.


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