CODEWITHSUNDEEP
sqljoinleft-joininner-join
Green
Green

Reputation: 13

Need help with a simple Join

Oi

Right to the problem.

SELECT *,t.id AS threadid FROM threads t 
LEFT JOIN players p on p.id = t.last_poster 
WHERE t.boardid = $boardid

I have two fields in threads called posterid and lastposterid. Which are the IDs of the thread starter / last poster. What I want to do is to get their names from players table.

But how?

Upvotes: 1

Views: 90

Answers (3)

Chris Diver
Chris Diver

Reputation: 19842

You can join to the same table twice and give the table a different alias.

This presumes that there always will be a first and last poster, if this is the case then you want an INNER JOIN rather than a LEFT JOIN, you will need to change the select statement to get the relevant fields. 

SELECT t.id AS threadid, playerFirst.name AS FirstPoster, playerLast.name as LastPoster 
FROM threads t
INNER JOIN
   players playerFirst ON playerFirst.id = t.posterid
INNER JOIN 
   players playerLast ON playerLast.id = t.lastposterid

Upvotes: 1

Brian Hooper
Brian Hooper

Reputation: 22074

How about...

SELECT *,
    (SELECT name
         FROM players
         WHERE players.id = threads.posterid) AS poster,
    (SELECT name
         FROM players
         WHERE players.id = threads.lastposterid) AS last_poster
    FROM threads;

Upvotes: 0

Robin Day
Robin Day

Reputation: 102538

You just need to join to your players table twice, like this.

SELECT
    threads.*,
    starterPlayer.*,
    lastPosterPlayer.*
FROM
    threads
LEFT OUTER JOIN
    players starterPlayer
ON
    starterPlayer.id = threads.posterid
LEFT OUTER JOIN
    players lastPosterPlayer
ON
    lastPosterPlayer.id = threads.lastposterid

Upvotes: 3

Related Questions