Column "?" Cannot be Null

[jimmyoneshot]

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?

[jimmyoneshot]

Hello 

[AyKay47]

There is no reason to be using table references, you are using 1 table.

This is occurring because the where clause in one of the queries is not being satisfied.

Why not have something like this instead:

 

select count(distinct id) as id_count, id from users where id = 1 or id = 997

[jimmyoneshot]

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

[AyKay47]

You are not using a join you are using a union.

Do you know what you are actually doing by setting @x:= to a field? Because you immediately overwrite the value that you set.

If you are not sure if one of these values will return a result set, use a join or subquery.

 

select distinct `type 1 users` as type_1,
count(id) as count,
(select distinct `type 2 users` from users where type = 2) as type_2
where type = 1; 

 

*This query is untested, may need tweaked*

[jimmyoneshot]

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" 

[AyKay47]

Yeah, I failed.

 

select distinct u1.id as type_1, count(id) as count, u2.id as type_2
left join users as u2
on (u2.type = 2)
from users as u1
where u1.type = 1;

[jimmyoneshot]

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.

[AyKay47]

If you want to grab multiple rows, then generate the query in a way that it will grab multiple rows.

[jimmyoneshot]

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

[jimmyoneshot]

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

[fenway]

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

Then please post the final working query.