List:General Discussion« Previous MessageNext Message »
From:Karl Uscroft Date:November 22 2001 8:45pm
Subject:How do I count using two tables
View as plain text  
Hi

Having a bit of bother with this one, wondering if anyone has the answer,

I've got two tables one is News items

News_ID    News    etc...
1                Today in .....etc
2                Hello world .....etc
3                Blar di blar....etc
4                And now time for something else
.
.
100            The last thing

and the other is comments on the news items (as well as other things on the
web site)

Type        Comment_ID        Comment  etc...
News        1                        What about today then...
News        1                        This is still today...........
News        4                        This parot is dead......
NotNews   4                        Hello..............

What I am trying to get is one SELECT statment that will give me

News_ID        News               Comment_Count  etc...
1                   Today in ...       2
2                   Hello world.       0
3                   Blar di blar        0
4                   And now ...etc  1


I've tried quite a few SELECTs using LEFT JOIN and HAVING and the best I can
do is a news list showing only Items that have Comments so the above would
have returned only NEWS_ID 1 and 4 but not giving 2 and 3 were I want
1,2,3,4.....100.

Please help, sorry if this is kids stuff but I'm only just starting with
MySQL.

Cheers Karl.

Thread
How do I count using two tablesKarl Uscroft22 Nov
  • Re: How do I count using two tablesAleksandar Bradaric23 Nov
  • Re: How do I count using two tablesDL Neil23 Nov