SQL Join query with a null in Main table

Hi guys so in simple terms I have a Main table “A” and then another table “B”. All the data I want is basically from table A, but to figure one part out I need to join with B. My problem is although the column im joining with a column in B is correct, sometimes the column in A will be Null and essentially have no relation to column B.

example:

Table A

ID | NAME | SECTOR | HAIR | AGE
1  | John |  5     | RED  | 20
2  | Doe  |  3     |BROWN | 22
3  | Mary |        |RED   | 20 
4  | Eric |  5     | RED  | 45

Table B

ID | SectorID | SectorNme
1  |    5     |  Plumbing
2  |    3     |  Admin

Now the real table has more fields and all but unfortunately cannot upload it due to privacy. But above is a simple idea what I wish to get at, now say I wish to get all people who are in plumbing and also have a null sector so my ideal return answer i want would be John and Mary.

But my WRONG query code which is

SELECT DISTINCT * 
FROM TableA  
left JOIN  TableB
ON TableA.SECTOR = TableB.SectorID
WHERE TableB.SectorNme= 'Plumbing' AND TableA.hair = 'red' AND tableA.age < 30

Obviously will just return John as Mary sectorID is null so no join. So this is probably a very SIMPLE fix but my brain is obviously not working so how can I go about including the likes of any NULL sectors from table A etc.

Answer

So you want to be SectorName = Plumbin and hair = red OR sector null? AND of course age <30

WHERE ((TableB.SectorNme= 'Plumbing' AND TableA.hair = 'red') OR TableA.sector is null ) AND tableA.age < 30

Advertisement

Answer