Subquery on a join

Question

I'm trying to do a query to filter on rows from one table and then join the results on another table and then filter out additional rows.

Here is the query I've written:

SELECT * 
  FROM (SELECT media.id AS id2, media.flagged AS flagged2, media.item_status AS status2 
          FROM media 
         WHERE flagged2 != 'nsfw' 
           AND status2 != 'deleted' 
     ORDER BY id2 DESC LIMIT 0,5) 
  JOIN media on info.mid = media.id 
 WHERE info.topic = 'food_drink' 
    OR info.topic='cooking' 
GROUP BY info.mid

I think I'm pretty close to getting the query working but I keep getting the message, "Every derived table must have its own alias." I've Googled this and from what I've read I need to alias parts of the subquery which I've tried but I still can't get it working.

Answer 1

Use:

SELECT * 
  FROM (SELECT media.id AS id2, media.flagged AS flagged2, media.item_status AS status2 
          FROM media 
         WHERE flagged2 != 'nsfw' 
           AND status2 != 'deleted' 
      ORDER BY id2 DESC LIMIT 0, 5) x
  JOIN media on info.mid = media.id 
 WHERE info.topic = 'food_drink' 
    OR info.topic='cooking' 
GROUP BY info.mid

See the x, just outside the bracket but before the join? That's what the error is about. The stuff inside the brackets after FROM and before JOIN is a derived table (AKA inline view), and MySQL requires that you specify a table alias for it. Then, when you reference any columns from inside it, you'd use x.id2/etc.