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

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64