jimmyoneshot

I think I've solved it the count was the problem. I just used a subquery to get counts

OK sorry about this ha. Thanks for the help. I'll see if I can come up with a better example

Thanks. I modified it to this:- select distinct u1.id as type_1, count(u1.id) as count, u2.id as type_2 from users as u1 left join users as u2 on (u2.type = 2) where u1.type = 1 The problem is this the result is then returned as this in a single row:- type 1 | count | type 2 7 4 8 Whereas I need all users to be returned like this so that I can loop through the result and echo them out on the front end:- id | Name | count | type 7 John 4 type 1 user 8 Paul 4 type 1 user 10 Steve 4 type 1 user 9 Jack 4 type 1 user 6 Jill 2 type 2 user 4 Gary 2 type 2 user I can't see how this can be done with subqueries based on the fact that I need a list of users. My actual where criteria for the queries I have is a bit more complicated as it is actually based on another left join which defines if users are registered but as I say I tried to simplify it here.

OK I modified your query to this:- select distinct users.id as type_1,count(id) as count, (select distinct users.id from users where type = 2) as type_2 FROM users where type = 1 Which resulted in the error:- "#1242 - Subquery returns more than 1 row"

Sorry maybe the example I gave was pretty bad because I tried to simplify it which was a mistake but basically I need to show user types as in I need to show all the user that are type 1 underneath one heading on my site and then all the user's that are type 2 underneath which means using OR wouldn't be relevant so it should look like this like this:- TYPE 1 USERS John Smith Paul Jones Jill Jackson TYPE 2 USERS Gary Stevenson Steve Arnold So maybe this is a better example of why I'm using join which again produces the error of either the top or bottom query returns no results:- SELECT DISTINCT @x:='TYPE 1 USERS' AS heading, COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.type = 1 UNION ALL SELECT DISTINCT @x:='TYPE 2 USERS' AS heading, COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.type = 2

Hello

I know this isn't a practical example but it's a simplified version of my problem. Basically if I have this query or something:- SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 1 UNION ALL SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 997 I get the error #1048 - Column 'id' cannot be null The first query on it's own returns 1 result whereas the second query on it's own returns 0 results but when they are unioned this error get's thrown. Including the COUNT seems to be what causes the problem. The following query however:- SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 1 UNION ALL SELECT DISTINCT COUNT(u1.id) AS u1_count, u1.id FROM users AS u1 WHERE u1.id = 2 works fine because the second query in this query returns a row. How can I get rid of this error please?