Mysql JOIN子查询

本文介绍了Mysql JOIN子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我应该如何查询哪里

• 我有(table1)行，在其他表中可以没有任何行或多个行引用该行
• 这些引用(表2)行的colum1可以为null或日期

我想让table1中的所有行的table2中的所有列column1都不为null或根本没有行.

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

当然，基本的sql就像这样:

Of course the basic sql goes like:

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

但是接下来会发生什么呢?

But what comes next?

推荐答案

您可以像SUM(CASE WHEN table2.col IS NULL THEN 1 ELSE 0 END) AS nullcount一样计算查询中出现null的次数，我假设table2.col是其中具有null日期的那个

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