Fetch data from three tables where a particular column from other two tables are null?-CodePudding

I have three tables: A, B and C.

Tables have the following columns:

A: id, name, shipdate
B: id, returndate
C: id, deliverydate

I have fetched data of Tables A and B using INNER JOIN and the ID Column.

SELECT  A.name, 
        A.shipdate, 
        B.returndate 
FROM A INNER JOIN B ON A.id = B.id

Same I have fetched data of Tables A nd C using INNER JOIN AND ID column.

But how to fetch data from Tables A, B and C where returndate is null and deliverydate is null?

Here's the query i tried:

SELECT   A.name, 
         A.shipdate, 
         B.returndate, 
         C.deliverydate 
FROM A 
INNER JOIN B ON A.id = B.id 
INNER JOIN C ON A.id = C.id 
WHERE B.returndate = null 
AND C.deliverydate = null

CodePudding user response：

Just constrain your condition in WHERE:

SELECT A.*
FROM A
INNER JOIN B
  ON A.id = B.id
INNER JOIN C
  ON B.id = C.id
WHERE 
  ISNULL(B.returndate) = 1 AND ISNULL(C.deliverydate) = 1

Instead of ISNULL(B.returndate) = 1 you can use B.returndate is null, but a statement like B.returndate = null will always return false.

CodePudding user response：

Using left join and IS NULL (= null won't find anything since null is <> to anything)

drop table if exists a,b,c;

create table a(id int, name varchar(3), shipdate date);
create table b(id int,returndate date);
create table c(id int,deliverydate date);

insert into a values(1,'aaa','2022-01-01'),(2,'bbb','2022-01-01'),(3,'ccc','2022-01-01'),(4,'ddd','2022-01-01');

insert into b values(1,'2022-01-01'),(2,'2022-01-01');

insert into c values(1,'2022-01-01'),(3,'2022-01-01');

SELECT A.name, A.shipdate, B.returndate, C.deliverydate 
FROM A 
left JOIN B ON A.id = B.id 
left JOIN C ON A.id = C.id 
WHERE B.returndate is null AND C.deliverydate is null;

 ------ ------------ ------------ -------------- 
| name | shipdate   | returndate | deliverydate |
 ------ ------------ ------------ -------------- 
| ddd  | 2022-01-01 | NULL       | NULL         |
 ------ ------------ ------------ -------------- 
1 row in set (0.001 sec)