Select on several {name,value} pairs I have a this schema:

CREATE TABLE Pairs(
   id    VARCHAR(8)  NOT NULL
   ,name  VARCHAR(40) NOT NULL
   ,value VARCHAR(1000) NOT NULL
   ,PRIMARY KEY (id,name));


I want to write a query to retrieve the id which is present in several rows with several constraints on name and value like:


name like 'forname%' AND value like 'CAMI%'
name like 'projet%'  AND value like '%gociation'
name ... AND  value ...
...


The cardinality of the constraints is unknown at compile time, it comes from a GUI.

I wrote this query:

select * from (
      SELECT * FROM Pairs WHERE ( name   =  'projet'   ) AND ( value like '%gociation' )
union SELECT * FROM Pairs WHERE ( name like 'forname%' ) AND ( value like 'CAMI%' )
) as u order by id


the result is:

id      name    value
AEMIMA  projet  renegociation
AENABE  projet  renegociation
AEREDH  projet  renegociation
AGOGLY  projet  renegociation
AHOGAL  projet  renegociation
AHSPID  projet  renegociation       &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;
AHSPID  fornameE    CAMILLE         &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;
AIOSAP  projet  renegociation
AIPNEU  projet  renegociation


Only the &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; marked lines are good and the id I want is AHSPID.

How can I eliminate the lines which doesn't match ALL the constraints?
I've found answer around Key/Value pairs terms, and the capacity involved is the inner-join:

SELECT DISTINCT
   Prospects.id,
   Prospects.prenom,
   Prospects.nom,
   Prospects.date,
   Prospects.departement
FROM
    Prospects
    INNER JOIN Pairs AS p1 ON Prospects.id = p1.id
    INNER JOIN Pairs AS p2 ON Prospects.id = p2.id
    INNER JOIN Pairs AS p3 ON Prospects.id = p3.id
    INNER JOIN Pairs AS p4 ON Prospects.id = p4.id
    INNER JOIN Pairs AS p5 ON Prospects.id = p5.id
    INNER JOIN Pairs AS p6 ON Prospects.id = p6.id
WHERE TRUE
AND (( p1.name like 'name%'    ) AND ( p1.value LIKE '%XXX%')) 
AND (( p2.name like 'forname%' ) AND ( p2.value LIKE '%CAMILLE%')) 
AND (( p3.name = 'codePostal'  ) AND ( p3.value = 'XXXXX')) 
AND (( p4.name = 'email'       ) AND ( p4.value LIKE '%XXXX%' )) 
AND (( p5.name like 'tel_%'    ) AND ( p5.value LIKE '%XXXXXX%')) 
AND (( p6.name = 'projet'      ) AND ( p6.value LIKE '%gociat%' ))
AND ( etape IN (-1,3,8 ))


The lines INNER JOIN...id and AND (( p... )) are generated by analysis of the request dynamically made by the GUI. The keyword TRUE is added to make all AND... lines identical, the first isn't different from the others.