I need to write a query, to return ‘prospect’ information, and related ‘unit’ information if there are any. But I am facing a difficulty that could not solve: unit_desire column in table prospect could be null value,which makes the result return null as whole. What I need is if unit_desire is null, then return prospect information only. if unit_desire is not null, then return both parts of information. How could I fix this issue?
SELECT prospect.*, unit.*
FROM prospect
LEFT unit
ON (prospect.unit_desired=unit.name)
WHERE prospect.id='100000057'
>Solution :
Pretty much you need an if – in the first case, return all the columns from both tables, otherwise only the columns from prospect table. In must RDBMS you could implement such logic with conditional branches (in a stored procedure, for instance).
But with vanilla SQL we can just take advantage that only one of the cases will return results (either unit_desired is null or it isn’t) … so we can just UNION the results of both cases. This is a set-oriented solution which is one way to think about SQL problems and solutions.
SELECT prospect.*, unit.*
FROM prospect
LEFT JOIN unit
ON (prospect.unit_desired=unit.name)
WHERE prospect.id='100000057' AND prospect.unit_desired is not null
UNION ALL
SELECT prospect.*
FROM prospect
WHERE prospect.id='100000057' AND prospect.unit_desired is null
I am not really so enthused about this though, since now you have one query that can return different numbers of columns … and that is generally a bad thing. I would prefer to always return the columns from both tables, even if there is no match in unit – you could possibly handle the rest in your presentation layer or on the application/client side.