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 |
The first idea (multiple COUNT()s and GROUP BYs) didn't work, probably because of the
way I was confusing the
use of table aliases with VIEWs. No matter, let's go to 'plan B'.
Following my usual 'formula' approach, breaking it down into smaller problems:
1 getting the data out of the clients tbl and grab all of the status rows
2 refine the first ratings criteria to a 'count'
3 do the same for the second ratings criteria
The first is a trivial join:
SELECT clientname, clients.ID, ratings.status-2, ratings.status-3
FROM clients, ratings
WHERE clients.ID = ratings.clientID
AND (ratings.status = '2' OR ratings.status = '3')
Just to be sure of my assumptions (remember I don't have enough info to be able to
debug/test!), please check
that this is ALL of the data you want included, and that none other exists/is missing.
This won't satisfy you - you don't want line after line of status data, you want that
info aggregated. Taking
status=2 first, we should go from:
SELECT clientname, clients.ID, ratings.status-2
FROM clients, ratings
WHERE clients.ID = ratings.clientID
AND ratings.status = '2'
to:
SELECT clientname, clients.ID, count(ratings.status-2)
FROM clients, ratings
WHERE clients.ID = ratings.clientID
AND ratings.status = '2'
GROUP BY clients.ID
- or should that be GROUP BY ratings.clientID? (they've 'fiddled' with some of the
finer points of GROUP BY
recently)
Trouble is this falls apart when we add status-3 - as we discovered earlier.
Let's review what COUNT() does. SQL groups the rows according to the WHERE clause and
then notes the number of
rows in the group. Ok, so we can do that too - let's imagine adding an extra column to
the table, and put a TRUE
into the column if it is relevant to the count, and FALSE if it is not. Yes, there is
an IF() in SQL (some very
'meaty' RTFM starts at 6.3 Functions for Use in SELECT and WHERE Clauses).
IF( ratings.status = '2', TRUE, FALSE )
So now we could simply count the TRUEs to arrive at the number of status=2 rows.
Substitute 1 for TRUE and 0 for
FALSE, and we can have SQL do that last calc for us, ie SUM( imaginary-column ).
SUM( IF( ratings.status = '2', 1, 0 ) )
Because there is no GROUP structure, we can replicate the same for status='3'.
SELECT clientname, clients.ID,
SUM( IF( ratings.status = '2', 1, 0 ) ) AS Status-2,
SUM( IF( ratings.status = '3', 1, 0 ) ) AS Status-3
FROM clients, ratings
WHERE clients.ID = ratings.clientID
BTW I am taking from your query the idea that status is a string not a numeric.
Another BTW is that appropriate indexing will speed things up considerably if you are
operating on larger
tables!
Beaten it this time?
Please advise,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
