mysql join 子查询结果,Mysql JOIN子查询

How should I do query where

I have (table1) row which can have none or several rows referencing to it in other table

These referencing (table2) rows have colum1 that can be null or date

I would like to have all rows from table1 which have all rows column1 not as null or no rows at all, in table2.

Of course the basic sql goes like:

SELECT table1.* FROM table1 JOIN table2 ON table2.id = table1.table2_id

But what comes next?

解决方案

You can count the occurences of null in your query like SUM(CASE WHEN table2.col IS NULL THEN 1 ELSE 0 END) AS nullcount, i assume table2.col is the one which has date of null in it

SELECT

table1.*,

SUM(

CASE

WHEN table2.col IS NULL

THEN 1

ELSE 0

END

) AS nullcount

FROM

table1

JOIN table2

ON table2.id = table1.table2_id

HAVING nullcount > 0