Incorrect COUNT values obtained

[Buchead]

Hi all,

 

Wondering if someone could point out what I'm doing wrong,

 

Have 3 tables:

 

`states`

 

stateID

state

clientID

date

 

 

`states_info`

 

stateID

stateText

position

 

 

`clients`

 

clientID

clientName

 

 

`states_info` contains unique info about various states a client can be in, with position being the order they appear in. `states` list can contain multiple entries for a client detailing the state they were in and the date. What I'd like to do is pull out a count of the number of clients in each state.

 

If I use:

 

SELECT COUNT(s.clientID) AS noClients, st.state, st.stateID FROM `states_info` AS st LEFT JOIN `states` AS s ON st.stateID=s.state GROUP BY st.stateID
ORDER BY st.position ASC

 

it will produce a count, however includes all clients that have been in a state. What I'd like it to only include the latest state (ie, `states_info` position is the highest number) a client is in.

 

 

Then when I want to view a list of clients only in a certain state how I can get a list of clients in which the viewed state is the latest one (ie, the highest position number in `states_info`).

 

SELECT c.*, si.state FROM `clients` AS c LEFT JOIN `states` AS s ON s.clientID=c.clientID LEFT JOIN `states_info` AS si ON s.state=si.stateID WHERE s.state=$value

 

Works but not ignoring clients who are in a higher states_info position.

 

 

Thanks for any pointers.

[fenway]

You need to figure out the "highest" state first, then join back