CODEWITHSUNDEEP
mysqlleft-joingreatest-n-per-group
Ing. Michal Hudak
Ing. Michal Hudak

Reputation: 5612

Mysql - LEFT JOIN - get first entry

I have this structure in MySql

tables

I am trying to get:

FIRST post, from LAST topic WHERE category is 'News'

In this example it is row from post where id = 2 as marked on image

row

So far I got this query:

SELECT *
FROM forum_post AS p 
LEFT JOIN forum_topic AS t ON p.topic_id = t.id
LEFT JOIN forum_category AS c ON t.category_id = c.id
WHERE c.title = 'News' AND t.id = MAX(t.id)
ORDER BY p.id ASC LIMIT 1

EDIT:

Dirty solution:

SELECT * FROM forum_post
WHERE topic_id = (SELECT MAX(id) FROM forum_topic WHERE category_id = 1)
ORDER BY id ASC LIMIT 1

Upvotes: 2

Views: 199

Answers (3)

gotqn
gotqn

Reputation: 43636

I haven't test it, but it shoud be something like this:

SELECT fm.remply
FROM forum_topic ft
JOIN forum_category fc
    ON ft.category_id = fc.category_id
    AND fc.title = 'News'
JOIN forum_post fm
    ON ft.id = fm.topic_id
ORDER BY ft.id DESC
        ,fm.id DESC
LIMIT 1

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can still use a joined query instead of a subquery to get the first post from last topic of your category,note the subquery in join will run only once to get the result set and in your case subquery will run for each iteration

SELECT * FROM
forum_post AS p 
  JOIN 
    (SELECT 
      t.id 
    FROM
      forum_topic AS t 
      JOIN forum_category AS c 
        ON t.category_id = c.id 
    WHERE c.title = 'News' 
    ORDER BY t.id DESC 
    LIMIT 1) t
  ON p.topic_id = t.id 
ORDER BY p.id ASC 
LIMIT 1

Upvotes: 1

TJ-
TJ-

Reputation: 14363

select fp.* from forum_post fp,
(select min(fp.id) from forum_post fp where topic_id in 
 (select max(ft.id) from forum_topic ft inner join forum_category fc 
     on fc.id = ft.category_id where fc.title = 'News'))T 
where fp.id = T.id

[In case there are no forum_posts, no row will be returned]

Edit: Updated [Although I haven't tried executing it]

Upvotes: 0

Related Questions