// post · 179

For future reference

author: Michael Dale ·

SELECT news.*, COUNT(comments.newsid) AS count FROM news
LEFT JOIN comments ON news.id=comments.newsid
GROUP BY news.id ORDER BY Id DESC


...don't mind me.

EDIT: Excellent down to 0.16 from 0.26 seconds. And down to 1 query from a shocking 162 on my little test. Very very good.

// comments

· Matthom guest
Must you show off your SQL skills? Heh.

Actually, I really love the power of LEFT JOIN.

Oooh, I love SQL.
#1 Comment Link
· Michael Dale user
Took me a while to get that query out! But it is very powerful. I've got the front page number of queries down from about 14 to 2!

Now I can get the number of comments at the same time as getting the news posts themselves. So two different tables at one time.
#2 Comment Link
· Matthom guest
Oooh, that soinds interesting. I, too, pull out everything at once - on some pages. But, like you said, I have to do a number of queries.

LEFT JOIN solves a lot of problems of multiple queries, but it acts really weird sometimes.

Also, it depends on how the tables are structured. If they are structured sloppily - like they are in my case - LEFT JOIN won't solve any problems.

The first step is solid relational database theory.
#3 Comment Link

// leave a comment

> click for comments help

HTML allowed: <a href="" title="" rel=""></a> <b></b> <blockquote cite=""></blockquote> <em></em> <i></i> <strike></strike> <strong></strong> <li></li> <ol></ol> <ul></ul>
ie: <b>bold</b>

Your comment may need to be reviewed before it is published.