CODEWITHSUNDEEP
mysql
Paulina04
Paulina04

Reputation: 11

How do I combine 2 MySQL queries into one query?

I'm coding a simple forum using PHP and MySQL.

On the forum main page I want to list all threads alphabetically and for each thread I want to show the most recent post with the username of the poster.

I know how to sort the threads table and I know how to pull the most recent post for a given thread from the posts table.

But I'm not sure how to combine both queries into one.

SELECT * 
FROM threads 
ORDER BY title

SELECT comment, username 
FROM posts 
WHERE thread_id =... 
ORDER BY post_datetime 
DESC LIMIT 1

I've tried:

SELECT t.title
FROM threads t
LEFT JOIN(
    SELECT comment, username, thread_id
    FROM posts
    ORDER BY post_datetime DESC LIMIT 1
) d
ON t.thread_id = d.thread_id 
ORDER BY t.title

But this only returns the titles of the forum threads (sorted).

Upvotes: 1

Views: 21

Answers (1)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

you should read about join:

SELECT t.title,  p.comment, p.username, p.thread_id
FROM threads t
INNER JOIN posts p
ON t.thread_id = p.thread_id 
ORDER BY t.title, p.post_datetime

Upvotes: 2

Related Questions