6 replies to this topic

[MichaelR]

Can you please tell me why this will work:

SELECT forum_thread.id, forum_thread.author, forum_thread.name, forum_thread.forum, forum_thread.isSticky, forum_thread.last_post_TS, members.id, members.first_name, members.last_name

        FROM forum_thread

        LEFT JOIN members

        ON forum_thread.author = members.id

        ORDER BY forum_thread.isSticky DESC, forum_thread.last_post_TS DESC

But this will not work:

SELECT forum_thread.id, forum_thread.author, forum_thread.name, forum_thread.forum, forum_thread.isSticky, forum_thread.last_post_TS, members.id, members.first_name, members.last_name

        FROM forum_thread

        LEFT JOIN members

        ON forum_thread.author = members.id, forum_thread.forum = 1

        ORDER BY forum_thread.isSticky DESC, forum_thread.last_post_TS DESC

I need to so that the forum_thread.forum is equal to 1 AND forum_thread.author is equal to members_.id
Please help. :(

[MichaelR]

Okay well I got it to work with:

SELECT forum_thread.id, forum_thread.author, forum_thread.name, forum_thread.forum, forum_thread.isSticky, forum_thread.last_post_TS, members.id, members.first_name, members.last_name

        FROM forum_thread

        LEFT JOIN members

        ON forum_thread.author = members.id

        WHERE forum_thread.forum = 1

        ORDER BY forum_thread.isSticky DESC, forum_thread.last_post_TS DESC

I guess it doesn't allow multiple ON variables?

[WingedPanther]

Try this:

SELECT forum_thread.id, forum_thread.author, forum_thread.name, forum_thread.forum, forum_thread.isSticky, forum_thread.last_post_TS, members.id, members.first_name, members.last_name
        FROM forum_thread
        LEFT JOIN members
        ON (forum_thread.author = members.id AND forum_thread.forum = 1)
        ORDER BY forum_thread.isSticky DESC, forum_thread.last_post_TS DESC

[MichaelR]

Nah, that didn't seem to work WingedPanther; it just shows all threads, not just threads in forum 1.

[WingedPanther]

Ahh... yes, that would require it in the where clause. Putting it in the join clause will just produce null records for those table columns where the forum is not 1.

[MichaelR]

Thanks though. :)

[Amy]

hey , try this

SELECT forum_thread.id, forum_thread.author, forum_thread.name, forum_thread.forum, forum_thread.isSticky, forum_thread.last_post_TS, members.id, members.first_name, members.last_name
FROM forum_thread
LEFT JOIN members
ON forum_thread.author = members.id AND forum_thread.forum = 1
ORDER BY forum_thread.isSticky DESC, forum_thread.last_post_TS DESC

i just replaced the comma with AND in ON condition ..

Good Luck !