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
