I'm trying to output all rows When using a filter with now success. When using left join all rows are not shown. Is there a way where I can output all rows even if there is no value? Below is a sample and the desired output.
Table A
a b
---------------
1 1
1 2
1 3
1 4
1 5
Table B
a c
--------------
1 100
2 100
5 100
SELECT a.a, a.b, b.c
FROM tableA a
LEFT JOIN tableB b
ON a.b = b.a
WHERE a.a = '1' AND b.c = '100'
Output
a b c
-------------------
1 1 100
1 2 100
1 5 100
Desired Output
a b c
-------------------
1 1 100
1 2 100
1 3
1 4
1 5 100
If you left join a to b, the value for b.c will be NULL where there is no corresponding row in table b .
And you filter by a.a = 1 and b.c = 100. If b.c is NULL, the comparison b.c = 100 will return NULL, too, and therefore not satisfy the WHERE condition.
Therefore change your WHERE condition to:
WHERE a.a = 1 AND (b.c IS NULL OR b.c = 100)