mySQL queries ?

[Mark_78]

What do you think to these mySQL queries ? could they be made better ?

SELECT cats.*, forums.*, members.MID, members.Username, topics.Title 
FROM pmb_categories as cats, pmb_forums as forums 
LEFT JOIN pmb_members as members 
ON (members.MID = forums.Last_Poster_ID ) 
LEFT JOIN pmb_topics as topics 
ON ( topics.TID = forums.Last_Topic_ID ) 
WHERE cats.CID = forums.CID ORDER BY cats.Position, forums.Position

SELECT stats.*, members.MID, members.Username 
FROM pmb_stats as stats, pmb_members as members 
WHERE stats.New_Member_ID = members.MID

[mod] Edited to fit the screen better... please try not to stretch out the screen. It makes it harder to read.

[BuzzLY]

When using any JOINS, in order to clear up potential confusion, you should JOIN all of your tables. The rule of thumb is that when using joins, ON statements define the links between primary and foreign keys, and WHERE statements define restrictions (such as "WHERE lastname = 'Smith'").

Therefore, I would write your first query as such:

SELECT cats.*, forums.*, members.MID, members.Username, topics.Title 
FROM pmb_categories as cats
JOIN pmb_forums as forums 
ON cats.CID = forums.CID 
LEFT JOIN pmb_members as members 
ON members.MID = forums.Last_Poster_ID
LEFT JOIN pmb_topics as topics 
ON topics.TID = forums.Last_Topic_ID
ORDER BY cats.Position, forums.Position

Notice in this case you are simply linking tables, and not limiting your resulting data to any subset -- there is no need for a WHERE clause. If you add a WHERE clause, it's simply to narrow down your dataset, such as "WHERE cats.name = 'general'"

[Mark_78]

Thanks, 🙂, and sorry about the my first post!