John, > 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.
Let's break it down into smaller problems: 1 getting the data out of the clients tbl 2 getting the first ratings criteria 3 getting the second ratings criteria The first is so trivial I won't bore you further. The second you also solved: add a join and a COUNT(*) group. Adding/combining with the third is where the problems lie! The key issue is the last word - "group". The second ratings criteria demands a different grouping. So can you declare the ratings table twice? SELECT clientname, clients.ID, COUNT(two.status), COUNT(three.status) FROM clients, ratings as two, ratings as three WHERE clients.ID = two.clientID AND two.status = '2' AND clients.ID = three.clientID AND three.status = '3' Apologies for my lack of time to prepare a sample db and test this - but you have the data already, so please give it a go. If it doesn't work, come back to us, and someone/I will show you how to solve the problem as a 'pivot table'. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php