SQL multiple joins SELECT query

Question

I've build the following query

$subforumsquery = "
        SELECT
          forums.*,
          posts.title   AS lastmsgtitle,
          posts.timee   AS lastmsgtime,
          posts.useraid AS lastmsguseraid,
          posts.useradn AS lastmsguseradn,
          users.photo   AS lastmsgphoto
        FROM forums
          LEFT JOIN posts
            ON (posts.forumid = forums.id)
          LEFT JOIN users
            ON (posts.useraid = users.id)
        WHERE forums.relatedto = '$forumid'
            and posts.type = 'post'
        ORDER BY `id` DESC";

I don't know why, but i get the same subforum twice even that there is only 1 sub forum.

BTW, is there any way to select only the last post insted of searching all?

Thanks!

please post your table schemata so we can see what you're selecting from. You can use DESCRIBE mytable or SHOW CREATE TABLE mytable — dnagirl
– dnagirl, Commented Mar 4, 2013 at 17:32

Muhammad Raheel · Accepted Answer · 2013-03-04 18:49:06Z

3

Use group by

SELECT
  forums.*,
  posts.title   AS lastmsgtitle,
  posts.timee   AS lastmsgtime,
  posts.useraid AS lastmsguseraid,
  posts.useradn AS lastmsguseradn,
  users.photo   AS lastmsgphoto
FROM forums
  LEFT JOIN posts
    ON (posts.forumid = forums.id)
  LEFT JOIN users
    ON (posts.useraid = users.id)
WHERE forums.relatedto = '$forumid'
    and posts.type = 'post'
GROUP BY forums.id
ORDER BY `id` DESC

EDIT :

Use MAX with a derieved query

SELECT
  forums.*,
  posts.title   AS lastmsgtitle,
  posts.timee   AS lastmsgtime,
  posts.useraid AS lastmsguseraid,
  posts.useradn AS lastmsguseradn,
  users.photo   AS lastmsgphoto
FROM forums
  LEFT JOIN (
        SELECT  
            * 
        FROM posts
        LEFT JOIN (
                SELECT 
                    MAX(id) AS ID 
                FROM posts 
                GROUP BY forumid
            ) AS l ON l.ID = posts.id
     GROUP BY forumid) AS posts
    ON posts.forumid = forums.id
  LEFT JOIN users
    ON (posts.useraid = users.id)
WHERE forums.relatedto = '$forumid'
    and posts.type = 'post'
GROUP BY forums.id
ORDER BY `id` DESC

edited Mar 4, 2013 at 18:49

answered Mar 4, 2013 at 17:31

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GuyChabra Over a year ago

worked great, is there any way to set MAX() on posts. I need only the last post.

JoDev Over a year ago

I think i'm right, but instead of adding a GROUP BY with LEFT JOIN, the INNER JOIN would be a better way...

GuyChabra Over a year ago

Where do I need to set the MAX()?

Oskar Persson Over a year ago

Why not a LIMIT 1 at the end?

GuyChabra Over a year ago

when I do "ON (posts.forumid = forums.id)" the query searching for all posts where forumid = $forumid. I need only the last post. how do i do it?

Collectives™ on Stack Overflow

SQL multiple joins SELECT query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related