MySQL IN operator SubQuery value, pls help

[stephenvs]

my subquery return this value = 69, 656, 133, 0. the main query only return value for 69. how to get a relevant record for the four values. pls help.

SELECT title, recipes.id
FROM recipes
WHERE id in (SELECT CONCAT(menudays.main1,', ',menudays.main2,', ',menudays.main3,', ',menudays.main4) as onecol FROM menudays WHERE menudays.date = '20110124' AND area_id=1
)

[r937]

first of all, you should not concatenate the values like that

if you really want to use an IN subquery for this, then you would need to use a UNION in the subquery, like this --

SELECT title
     , recipes.id 
  FROM recipes 
 WHERE id IN 
       ( SELECT main1
           FROM menudays 
          WHERE menudays.date = '20110124' 
            AND area_id = 1
         UNION 
         SELECT main2
           FROM menudays 
          WHERE menudays.date = '20110124' 
            AND area_id = 1
         UNION 
         SELECT main3
           FROM menudays 
          WHERE menudays.date = '20110124' 
            AND area_id = 1
         UNION 
         SELECT main4
           FROM menudays 
          WHERE menudays.date = '20110124' 
            AND area_id = 1
       )

the source of your difficulties is the menudays table, which should really be redesigned (to give you only one of several reasons, what happens if you add a 5th menu? all your queries have to change)

even without redesigning, it's still better to use a join rather than an IN subquery --

SELECT recipes.title
     , recipes.id 
  FROM menudays 
INNER
  JOIN recipes 
    ON recipes.id IN ( main1,main2,main3,main4 )
 WHERE menudays.date = '20110124' 
   AND menudays.area_id = 1

[stephenvs]

Thanks so much for that tip,