Mysql syntax using IN help!

Question

Hi, i have a pictures table : pictures(articleid,pictureurl)

And an articles table : articles(id,title,category)

So, briefly, every article has a picture, and i link pictures with article using articleid column. now i want to select 5 pictures of articles in politic category.

i think that can be done using IN but i can't figure out how to do it.

Note: Please only one query, because i can do it by selecting articles firstly then getting the pictures.

Thanks

Answer 1

To get five pictures from articles in a category you could do this:

SELECT pictures.pictureurl
  FROM articles, pictures
 WHERE articles.id = pictures.articleid AND articles.category = 'politic'
 ORDER BY [your sort criteria]
 LIMIT 5;

You could consider rephrasing the question a bit.

Answer 2

If you are looking for an IN query instead of a JOIN this is an alternative to Alex's query:

SELECT pictureurl 
  FROM pictures 
  WHERE arcticleid IN (SELECT id FROM articles WHERE category='politic') 
  LIMIT 5

Answer 3

You don't really need to use IN for this. IN serves when you nest queries or when you have a known set of values to check against.

To select 5 random images in the politics category:

SELECT pictureurl FROM articles, pictures WHERE pictures.articleid = articles.id AND articles.category = 'politics' ORDER BY RAND() LIMIT 5

Answer 4

Rewritten for clarification (see comments):

If you like to keep your JOIN criteria separated from your SELECT criteria, you can write something like the below:

SELECT pictureurl
FROM pictures
JOIN articles ON id = articleid
WHERE category LIKE 'politics'
ORDER BY RAND()
LIMIT 5

I find the intent slightly clear when it's written like that, and maybe it's just me, but I have encountered complex queries written in the SELECT * FROM a, b, c form that worked under MySQL 4 which choke MySQL 5, whereas the above format works fine with both.

Also, if you use uniform ID column names for conformity, and to avoid confusing yourself in more complex scenarios, you can use the USING clause instead. So if articles ID column is also named articlesid, the JOIN could be written like so:

SELECT pictureurl
FROM pictures
JOIN articles USING (articleid)
...