[SOLVED] IF / WHERE, not really sure. Please help!

[mfallon]

So I have the following SQL statement, which I am able to turn in to a nicely formatted table without any issues.

 

The problem I have is that the statement returns more rows than I want it to.

 

As you can see there are numerous typeid's and I only want to display data for those users who have an entry with a type id between 4 and 8. The key is that I don't think I can use a WHERE because it would exclude the other types from being counted.

 

Can anyone shed any light on how I can do this in SQL?

 

Thanks, Matt

 

SELECT users.id,users.username,straights.username,users.firstname,users.lastname,COUNT(straights.username) AS stotal,MIN(straights.date) AS mindate,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 1) AS stype1tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 2) AS stype2tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 3) AS stype3tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 4) AS stype4tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 5) AS stype5tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 6) AS stype6tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 7) AS stype7tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid =  AS stype8tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 9) AS stype9tot,
(SELECT COUNT(straights.typeid) FROM straights WHERE users.username = straights.username AND straights.status = 1 AND straights.typeid = 10) AS stype10tot
FROM straights
JOIN users ON straights.username = users.username
WHERE straights.status = '1' AND users.status = '1'
GROUP BY straights.username
ORDER BY users.id

[mfallon]

Scratch that, I've decided to do it in PHP.

 

Thanks anyway.