CODEWITHSUNDEEP
mysqlsql
Engineering Machine
Engineering Machine

Reputation: 642

How to LIMIT a join in MySQL?

I have two tables:

news (main table):

+----+-------------------------------+
| id | title                         |
+----+-------------------------------+
|  2 | The title of the story is...  |
+----+-------------------------------+

news_images (junction table)

+---------+--------------------------------------+----------------------------+
| news_id | image_name                           | date                       |
+---------+--------------------------------------+----------------------------+
|       2 | 57a8cab7c09306ca2bbaf02c413aa3d5.jpg | 2015-03-30 16:10:35.743337 |
+---------+--------------------------------------+----------------------------+
|       2 | da98cab7c09306ca2bbaf02c413ab2a9.jpg | 2015-03-30 16:10:30.120232 |
+---------+--------------------------------------+----------------------------+

I want to perform a query that will select all columns from news and get the first row from news_images that corresponds to the id from news.

Initially, my query was:

SELECT * FROM news n, news_images ni WHERE n.id = ni.news_id;

This SQL query was returning all images from news_images table that have the id from news. Later, after scanning through Stackoverflow and MySQL docs, I have modified my query to:

SELECT n.title, ni.news_image FROM news n, news_images WHERE n.id = ni.news_id AND ni.news_id
    IN (SELECT news_id FROM news_images LIMIT 1);

Although, I have the latest MySQL installed, I get the following error:

{ [Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 

'LIMIT & IN/ALL/ANY/SOME subquery']
  code: 'ER_NOT_SUPPORTED_YET',
  errno: 1235,
  sqlState: '42000',
  index: 0 }

Is there any other way I can limit the join in MySQL? Thank you

Upvotes: 2

Views: 93

Answers (1)

jarlh
jarlh

Reputation: 44795

Do a LEFT JOIN to also get articles without any images. Use NOT EXISTS to find only images where no older image exists for same news_id.

SELECT n.title, ni.image, news_images.*
FROM news n LEFT JOIN news_images ni ON n.id = ni.news_id
AND not exists (select 1 from news_images ni2
                where ni.news_id = ni2.news_id
                  and n2i.timestamp < ni.timestamp)

Upvotes: 2

Related Questions