Re: Urgent - SQL left join bug?

On 06/21/2017 11:06 AM, Kaijiang Chen wrote:
> I ran a SQL:
> select pha.id,ph.herb_id,pha.name,ph.weight
> from prescription_herbs as ph *left join* pharmacy_herbs as pha on
> ph.herb_id=pha.herb_id
> where ph.prescription_id=116285 and ph.deleted_at is null and
> pha.deleted_at is null and pha.pharmacy_id=22 order by ph.herb_id;
>
> Expected:
>
> It should have 10 rows because the SQL "select * from prescription_herbs as
> ph where ph.prescription_id=116285 and ph.deleted_at is null" returned 10
> rows and I'm using *LEFT JOIN *in the above SQL.
>
> Actual Result:
>
> It returned only 9 rows and the result is the same as JOIN (not LEFT JOIN).

Note that you have the condition "pha.pharmacy_id=22" in the WHERE part
of the query. That filters out rows with no matching pharmacy_herbs
rows, because pha.pharmacy_id is NULL for non-matching rows.

Put the "pha.pharmacy_id=22" condition in the ON join qual part instead.
And for readability and consistency, I'd suggest putting the
"pha.deleted_at is null" qual in the ON clause too, although that won't
affect the result:

select pha.id,ph.herb_id,pha.name,ph.weight
from prescription_herbs as ph
left join pharmacy_herbs as pha on ph.herb_id=pha.herb_id and
pha.deleted_at is null and pha.pharmacy_id=22
where ph.prescription_id=116285 and ph.deleted_at is null
order by ph.herb_id;

- Heikki