CODEWITHSUNDEEP
sqlmysqljoingreatest-n-per-group
jonagoldman
jonagoldman

Reputation: 8754

LIMIT 1 inside JOIN from specific tables in MySQL?

I want to show the user last activity. (you can read my last question for more info about it)

I need to do a big JOIN (6+ tables).

I need to get only one row per ID but there are some tables that contain more than one row per ID. For example an article ID can have only one row in the 'votes' table but it can have a lot in the 'images' table because there can be more than one image per article, but I want just one image.

So I need to do something like this (of course this code is wrong):

SELECT table1.item, table2.item, table3.item, table4.item, table5.item 
FROM table1
LEFT OUTER JOIN ... ON ... AND ...
LEFT OUTER JOIN ... ON ... AND ...
LEFT OUTER JOIN ... ON ... AND ... LIMIT 1
LEFT OUTER JOIN ... ON ... AND ... LIMIT 1
WHERE table1.item = ?;

How can I accomplish that?

Upvotes: 5

Views: 5893

Answers (1)

AndreKR
AndreKR

Reputation: 33658

This is similar to http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

Upvotes: 2

Related Questions