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