1

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       <<<<<<<<<<<<
AHSPID  fornameE    CAMILLE         <<<<<<<<<<<<
AIOSAP  projet  renegociation
AIPNEU  projet  renegociation

Only the <<<<<<<<<<<< marked lines are good and the id I want is AHSPID.

How can I eliminate the lines which doesn't match ALL the constraints?

1

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.

  • Yep, inner joins are the answer to determining the links between different rows of data. – RLF Sep 9 '13 at 0:47
  • 2
    Really, the various ( Prospects.id = p1.id ) conditions should be at ON clauses and not in the WHERE clause. No matter if MySQL allows such non-standard syntax, every INNER JOIN should be coupled with an ON. – ypercubeᵀᴹ Sep 9 '13 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.