This should do the trick (untested): SELECT ClientName, Count(*) FROM Clients LEFT JOIN Ratings ON Clients.ID = Ratings.ClientID GROUP BY ClientName HAVING Ratings.Status = 2 OR Ratings.Status = 3
Advertising
regards, Peter Software Engineer / Trainer Vervoorn IT ----- Original Message ----- From: "John Hawkins" <[EMAIL PROTECTED]> To: "php-db list" <[EMAIL PROTECTED]> Sent: Thursday, February 14, 2002 3:11 AM Subject: [PHP-DB] counting multiple columns based on different values > I'm gonna go out on a limb and guess that I'm missing > something obvious (and easy) because this sure seems > like it should be able to be done. > > Here's the issue: I need to pull the client name and > ID out of one table and then, count the records in a > different table (called ratings) that match two > different criteria. > > If I was doing 2 different sql statments, they would > look like this: > > select clientname, clients.ID, count(*) FROM clients, > ratings WHERE clients.ID = ratings.clientID AND > ratings.status = '2' > select clientname, clients.ID, count(*) FROM clients, > ratings WHERE clients.ID = ratings.clientID AND > ratings.status = '3' > > In a perfect world, I'd be able to receive the > following data from a single query: > > > | ClientName | ClientID | Status-2 | Status-3| > | Bob | 28 | 103 | 87 | > | Steve | 29 | 11 | 106 | > | Jerry | 30 | 50 | 82 | > > I sure hope I explained that well enough. > > Thanks! > > John > > __________________________________________________ > Do You Yahoo!? > Send FREE Valentine eCards with Yahoo! Greetings! > http://greetings.yahoo.com > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
