WHERE [1 of these values] IN [1 of these values]

Question

I have the following query :

SELECT A.id FROM logsen_alertes A
WHERE  
( SELECT LA2.type_colocation_id
  FROM logsen_liaisons_annonces_types_colocations LA2
  WHERE LA2.annonce_id = 25 AND LA2.annonce_type = 4
) 
IN
( SELECT L4.souhait
  FROM logsen_liaisons_alertes_souhaits L4
  WHERE L4.alerte_id = A.id
)

This query works well when my first subquery returns only 1 value, because that's how works IN(), looking for 1 unique value in a set of values. When my 1st subquery returns 2 or more values, MySQL returns me "Subquery returns more than 1 row". How can I make my query works when the first subquery returns several values ? Something like "WHERE [any of these values] i found in [ny of these values]" ?

score 1 · Answer 1 · answered Mar 08 '13 at 11:42

Try:

SELECT DISTINCT A.id FROM logsen_alertes A
JOIN logsen_liaisons_alertes_souhaits L4 ON L4.alerte_id = A.id
JOIN logsen_liaisons_annonces_types_colocations LA2
  ON LA2.type_colocation_id = L4.souhait AND LA2.annonce_id = 25 AND LA2.annonce_type = 4

score 1 · Answer 2 · answered Mar 08 '13 at 11:42

Try this:

SELECT 
        A.id 
FROM 
        logsen_alertes A
        INNER JOIN logsen_liaisons_alertes_souhaits L4
               ON L4.alerte_id = A.id
        INNER JOIN logsen_liaisons_annonces_types_colocations LA2
               ON LA2.type_colocation_id = L4.souhait 
WHERE 
         LA2.annonce_id = 25 AND LA2.annonce_type = 4

score 0 · Answer 3 · answered Mar 08 '13 at 11:43

0

SELECT DISTINCT A.id FROM logsen_alertes AS A
INNER JOIN logsen_liaisons_alertes_souhaits AS L4
        ON (L4.alerte_id = A.id)
INNER JOIN logsen_liaisons_annonces_types_colocations AS LA2
        ON (LA2.type_colocation_id = L4.souhait)
WHERE LA2.annonce_id = 25 AND LA2.annonce_type = 4

Should work

answered Mar 08 '13 at 11:43

Ed Heal

59,252
17
87
127

works like a charm. I prefered this one to the other answer, cause I heard every query restriction should be placed in WHERE instead of JOIN. – VaN Mar 08 '13 at 11:52
@user2083586 - Dunno where you heard that nonsense. Please delete it from your mind - it is old school – Ed Heal Mar 08 '13 at 11:54
Then I don't understand why you put "LA2.annonce_id = 25 AND LA2.annonce_type = 4" in a WHERE statement, instead of putting it in the JOIN statement. Could you clarify this choice ? – VaN Mar 08 '13 at 12:08
@VaN - Because it is a clause that is not linking the two tables together. Just a predicate on one table. – Ed Heal Mar 08 '13 at 12:11

WHERE [1 of these values] IN [1 of these values]

3 Answers3