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.

just a quick question...where did you get the "info"? is it an alias or a table? — hallie, Jun 24 '10 at 01:23
@hallie: Given that Eric hasn't used table aliases, I believe it's a table. Eric is going to have to let us know if references to `INFO` should be in this query, otherwise how they relate to the derived table... — OMG Ponies, Jun 24 '10 at 01:38
I suspect that ...JOIN media on info.mid = media.id... should actually be ...JOIN **info** on info.mid = media.id... — , Jun 24 '10 at 11:10

score 3 · Accepted Answer · answered Jun 24 '10 at 01:26

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.

Ah that makes sense. I thought the part I had to alias was in the subquery. Just rewrote the query based on your input and it's working perfectly! — Eric, Jun 24 '10 at 11:58

Subquery on a join

1 Answers1