CODEWITHSUNDEEP
mysqlsql
Durgesh Pandey
Durgesh Pandey

Reputation: 119

Also show row with NULL value

When I hit the below code without where clause, it shows row with NULL value but when I put where clause like below then there is no row with NULL value.

I want the row with NULL value with where clause.

SELECT * 
FROM project_category as a 
LEFT OUTER JOIN  project_estimate_detail  as b 
  ON a.id = b.project_cat_id 
where b.project_cat_id not in ('21','22','2')

Upvotes: 0

Views: 40

Answers (3)

dnoeth
dnoeth

Reputation: 60513

You join on a.id = b.project_cat_id and then filter on the inner table, b.project_cat_id, which removes all the NULLs.

Simply change your WHERE-condition to use the join column from the outer table:

SELECT * 
FROM project_category as a 
LEFT OUTER JOIN  project_estimate_detail  as b 
  ON a.id = b.project_cat_id 
WHERE a.id not in ('21','22','2')

Upvotes: 1

Priyanshu
Priyanshu

Reputation: 881

SELECT *
FROM   project_category AS a
       LEFT OUTER JOIN project_estimate_detail AS b
                    ON a.id = b.project_cat_id
WHERE  b.project_cat_id NOT IN ( '21', '22', '2' )
       AND b.project_cat_id IS NULL

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

SELECT * 
FROM project_category as a 
LEFT OUTER JOIN  project_estimate_detail  as b 
   ON a.id = b.project_cat_id 
where (b.project_cat_id IS NULL) OR (b.project_cat_id not in ('21','22','2'))

When b.project_cat_id is NULL then NOT IN evaluates to NULL. So you have to explicitly check for NULL using the IS NULL expression.

Upvotes: 2

Related Questions