CODEWITHSUNDEEP
phpmysqlsqljoinpdo
Sakuya
Sakuya

Reputation: 680

How to join 3 tables with different data between them?

I'm not too good with explaining things, apologies.

I have 3 tables that are similar to the below:

users
    id
    username
threads
    id
    title
    user_id
    lastpost_id
posts
    id
    content
    thread_id
    user_id

On a page listing forum threads, I want the username of both the thread author, and the last post author of that thread to be displayed, I'm attempting to achieve this in a single query.

My query looks like this:

SELECT t.*,u.username FROM threads t
INNER JOIN users u ON t.user_id=u.id
INNER JOIN posts p ON t.lastpost_id=p.id
ORDER BY t.id DESC

The first join enables me to get the username of the user id that started the thread.

The second join is what I'm not sure on, it can get me the user id but how do I get the username from that, as a 3rd join?

Upvotes: 0

Views: 635

Answers (3)

GolezTrol
GolezTrol

Reputation: 116110

You can select the same table multiple times if you give it a different alias. You can give the fields aliases too:

SELECT 
  t.*,
  tu.username as threadusername, /* Result field is called 'threadusername' */
  p.*,
  pu.username as lastpostusername 
FROM threads t
INNER JOIN users tu ON t.user_id=tu.id /* thread user */
INNER JOIN posts p ON t.lastpost_id=p.id
INNER JOIN users pu ON p.user_id=pu.id /* post user */
ORDER BY t.id DESC

Upvotes: 1

Aninda Bhattacharyya
Aninda Bhattacharyya

Reputation: 1251

I don't know if I got it correctly, but as per my understanding you can have a inner query to fetch the thread ids and then have a outer query to fetch the posts based on the thread id, have a max on post id and group by user id. Also join to user to have the name. Hope that helps.

Upvotes: 0

Zoltán Tamási
Zoltán Tamási

Reputation: 12754

You can join to a joined table like this:

SELECT t.*,u.username,u2.username FROM threads t
INNER JOIN users u ON t.user_id=u.id
INNER JOIN posts p ON t.lastpost_id=p.id
INNER JOIN users u2 ON p.user_id=u2.id
ORDER BY t.id DESC

Note, I haven't had time to test it, but it should work (at least in MySQL).

Upvotes: 0

Related Questions