MySQL statement not working properly?

[Skylight_lady]

Hi, I have the following code:

 

SELECT *, count(a.id) as number FROM a, b, c WHERE a.id = b.userid AND a.type = b.type AND b.status = 'Enable' AND c.same_type = a.type AND c.threshold >= (SELECT *, count(a.id) as numbers FROM a, b WHERE a.id = b.userid AND a.type = b.type AND b.status = 'Enable') ORDER BY number ASC

 

Everything works fine but when i add:

(SELECT c.threshold FROM c WHERE c.same_type <= a.type)

 

It doesn't make a difference if i use the less than or greater than. If i add a number instead of the full statement then it works fine. I need to be able to get the number from the database. It would be easier if i could add the counted "number" instead of the second sql statement

 

 

[fenway]

You can't use SELECT * in a subselect like that.

[Skylight_lady]

mmmmm ..... ok. I think i have it sorted by getting rid of the second select statement and putting in:

HAVING count(a.id) >= c.threshold

[kickstart]

Hi

 

You appear to have reversed the comparison operator between your original statement and you new one.

 

Also you probably want a GROUP BY clause.

 

All the best

 

Keith

[Skylight_lady]

Yes i realised that when i added more different rows to the table and it wasn't giving me the corrct result. I then used GROUP BY and works perfectly. Thanks for your help, it's tough when your brain goes thru too many different solutions.