--- In firebird-support@yahoogroups.com, Kjell Rilbe wrote:

>
> hay77772000 wrote:
>
> > However, we need to search for records in A which have a certain
> > set of settings eg setting1=a, setting2=b, setting3=c, setting4=d.
> > The setting names can change across queries, as can the number of
> > settings we need to match in a particular query.
> >
> > The only way I have figured out to perform such a query is to
> > self-join the Setting table multiple times (once for each setting
> > value I want to query on). So for 7 settings we get:
> >
> > SELECT * FROM A
> > INNER JOIN SETTING S1 ON A.ID = S1.ID
> > INNER JOIN SETTING S2 ON A.ID = S2.ID
> > INNER JOIN SETTING S3 ON A.ID = S3.ID
> > INNER JOIN SETTING S4 ON A.ID = S4.ID
> > INNER JOIN SETTING S5 ON A.ID = S5.ID
> > INNER JOIN SETTING S6 ON A.ID = S6.ID
> > INNER JOIN SETTING S7 ON A.ID = S7.ID
> > WHERE S1.NAME="setting1" AND S1.VALUE="a"
> > AND S2.NAME="setting2" AND S2.VALUE="b"
> > AND S3.NAME="setting3" AND S3.VALUE="c"
> > AND S4.NAME="setting4" AND S4.VALUE="d"
> > AND S5.NAME="setting5" AND S5.VALUE="e"
> > AND S6.NAME="setting6" AND S6.VALUE="f"
> > AND S7.NAME="setting7" AND S7.VALUE="g"
>
> I'm tired and overwork so I might not be thinking straight, but
> maybe this would work (untested):
>
> SELECT * FROM A
> WHERE (
> SELECT COUNT(*)
> FROM SETTING
> WHERE SETTING.ID = A.ID
> AND (NAME="setting1" AND VALUE="a")
> OR (NAME="setting2" AND VALUE="b")
> OR (NAME="setting3" AND VALUE="c")
> OR (NAME="setting4" AND VALUE="d")
> OR (NAME="setting5" AND VALUE="e")
> OR (NAME="setting6" AND VALUE="f")
> OR (NAME="setting7" AND VALUE="g"))
> ) = 7
>
> If it doesn't work, then maybe it can inspire you to find something
> along the same lines that does. :-)

Not a bad idea, provided you add one further parenthesis and the
combination id, name and value is unique.

Though I do have one thing to add to the original query: The chosen
plan will greatly influence how quick or slow it is to execute. My
guess is that having indexes on ID will be useful, whereas an index on
NAME will be horribly slow. Trying

> > SELECT * FROM A
> > INNER JOIN SETTING S1 ON A.ID = S1.ID
> > INNER JOIN SETTING S2 ON A.ID = S2.ID
> > INNER JOIN SETTING S3 ON A.ID = S3.ID
> > INNER JOIN SETTING S4 ON A.ID = S4.ID
> > INNER JOIN SETTING S5 ON A.ID = S5.ID
> > INNER JOIN SETTING S6 ON A.ID = S6.ID
> > INNER JOIN SETTING S7 ON A.ID = S7.ID
> > WHERE (S1.NAME="setting1" AND S1.VALUE="a"
> > AND S2.NAME="setting2" AND S2.VALUE="b"
> > AND S3.NAME="setting3" AND S3.VALUE="c"
> > AND S4.NAME="setting4" AND S4.VALUE="d"
> > AND S5.NAME="setting5" AND S5.VALUE="e"
> > AND S6.NAME="setting6" AND S6.VALUE="f"
> > AND S7.NAME="setting7" AND S7.VALUE="g")
> > OR 2=0

could be sufficient.

HTH,
Set