how to return row count of mysql sub query
I have the following SQL query:
SELECT games.id, games.GameTitle FROM games
WHERE EXISTS (
SELECT filename FROM banners
WHERE banners.keyvalue = games.id
AND banners.filename LIKE '%front%'
)
which is not quite correct for my use
what I'd like is something like:
SELECT games.id, games.GameTitle
FROM games WHERE EXISTS (
COUNT(SELECT filename FROM banners
WHERE banners.keyvalue = games.id AND b开发者_JS百科anners.filename LIKE '%front%') > 1
)
i.e. only select when the subquery retrieves more than 1 row.
Simply like that :
SELECT games.id, games.GameTitle
FROM games
WHERE (
SELECT COUNT(filename)
FROM banners
WHERE banners.keyvalue = games.id AND banners.filename LIKE '%front%'
) > 1
SELECT games.id, games.GameTitle
FROM games
WHERE EXISTS (SELECT COUNT(filename)
FROM banners
WHERE banners.keyvalue = games.id
AND banners.filename LIKE '%front%'
HAVING COUNT(filename)>1)
精彩评论