MYSQL Count nulls

[dsp77]

Hello, i have 3 different tables with all rows that can be null. The question is how to count the nulls from all tables or if there is at least one null?

[fenway]

How are these tables related?

[dsp77]

they all are related by userid(int)

[fenway]

What do you mean "they can all be null"? What can be null?

[dsp77]

lest say this table

CREATE TABLE IF NOT EXISTS `fishing` (
  `userid` int(11) NOT NULL,
  `fav_fish` varchar(255) DEFAULT NULL,
  `fav_lake` varchar(255) DEFAULT NULL,
  KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

a user fills information in more tables with exactly the same structure (different names), some will be left null and at the end i want to find how many nulls are in x tables. I tried some query's with no success:( 

[DavidAM]

Count rows where one column or the other (or both) is NULL

SELECT COUNT(userid) FROM fishing
WHERE fav_fish IS NULL
OR fav_lake IS NULL

 

Count rows where BOTH columns are NULL

SELECT COUNT(userid) FROM fishing
WHERE fav_fish IS NULL
AND fav_lake IS NULL

 

Of course, I don't think I would write the row if neither value was provided, so the query to count users who do not have a fishing record could be (depending on the name of your users table):

SELECT COUNT(users.userid)
FROM users LEFT JOIN fishing ON users.userid = fishing.userid
WHERE fishing.userid IS NULL

 

[dsp77]

Sorry DavidAM but i forgot to add userid UNIQUE index thank you anyway

[DavidAM]

I figured the userid would be unique. It would not be much use otherwise. The COUNT(userid) provides a count of the rows that satisfy the WHERE clause. You can use COUNT(*) if it makes you more comfortable. I prefer to count the primary key column because I believe it is more efficient. I have no proof that it is, so don't ask me to provide my sources. I just figure it is -- or can be.

 

If that is not what you were referring to, please elaborate on why you think my solution is not correct. Or how it failed when you tried it.