Peter Cos Peter Cos - 6 days ago 5
SQL Question

sql join returns fields with null value

I have the following query:

SELECT *
FROM tableA.A
LEFT JOIN tableB AS B ON B.id=A.id
LEFT JOIN tableC AS C ON C.id=A.id2
LEFT JOIN tableD AS D ON D.id=A.id3
WHERE D.id = '124' AND A.field = 1
GROUP BY A.id ORDER BY D.sortorder


The structure above is identic with my real query and i want to mention that all tables i used in the query are valid and rows are populated with numeric and alphabetic characters.There is no NULL value anywhere.

The problem is that, after i execute this query, it returns some fields with NULL values even though they are not null.

I tried to explain as good as i could,but it's a strange behaviour and i couldn't find anything on google.

If it's not a common issue and it's hard to find the mistake, maybe some suggestions would help me find the bug.

Thank you in advance

UPDATE I want to apologize.The problem was caused by an enter at the end on table A, that's why it returned NULL because there was actually no match.Thank you for your help

Answer

Try this query:

SELECT *
FROM tableA.A 
LEFT JOIN tableB AS B ON B.id=A.id 
LEFT JOIN tableC AS C ON C.id=A.id2 
LEFT JOIN tableD AS D ON D.id=A.id3 and D.id = '124'
WHERE A.field = 1 
GROUP BY A.id
ORDER BY COALESCE(D.sortorder,0)

Conditions on the right table of a LEFT JOIN should be placed inside the ON clause , not the WHERE clause.

If that doesn't work either, then I thing you misunderstood the LEFT JOIN purpose . It is used to keep all the records from the master table(A in your case) and discard all the data that doesn't match from the detail table, so , there will be NULL value when no match fouhd .

Comments