SQL query is empty in left join

user_11230127 0
Viewed 56

I have a table with profiles holding information on accounts on my site. It's possible to promote a profile on different pages ex. search page and frontpage until an expire date.

Promotions is stored in the table profile_promo and joined by profile_id.

My problem is that on the search page I want to show all profiles and left join with the promotion table

LEFT JOIN profile_promotion pp ON pp.profile_id = profile.id 
WHERE (pp.page = 'search' OR pp.page is NULL) and pp.expire >= now()

This works fine as long as there is a promotion on the search page or if there is no promotions at all.

If there is only a promotion on the frontpage I don’t get any results at all for my search page because of the WHERE.

But I still want the information from the profile regardless if there are promotions or not. Due note, that a profile can have promotion on multiple pages at the same time (maybe that changes some logic)

I’m not good at SQL so how do I solve this?

sql
2 Answers

Using where clause with LEFT JOIN converts it to INNER JOIN ultimately. You need to adjust your conditions as -

LEFT JOIN profile_promotion pp ON pp.profile_id = profile.id 
                               AND (pp.page = 'search' OR pp.page is NULL)
                               AND pp.expire >= now()
user_36277560

As you now know the correct logic for matching non-expired, "search" promotions is:

FROM profile p LEFT JOIN
     profile_promotion pp
     ON pp.profile_id = p.id  AND
        pp.page = 'search' AND
        pp.expire >= now()

The logic of a LEFT JOIN is that it returns all rows in the first table, even when the ON conditions evaluate to NULL.

For this reason, filtering conditions on the second table should be in the ON clause. Filtering conditions on the first table only should be in the WHERE clause.

Your code:

FROM profile p LEFT JOIN
     profile_promotion pp
     ON pp.profile_id = p.id  AND
WHERE (pp.page = 'search' OR pp.page is NULL) AND
      pp.expire >= now()

Turns this into an INNER JOIN, because of the second condition. When the LEFT JOIN does not find a match, then pp.expire will be NULL and fail the condition.

You could try to get around that by doing:

WHERE (pp.page = 'search' OR pp.page is NULL) AND
      (pp.expire >= now() OR pp.expire IS NULL)

However, this is still not the same logic. This will catch when there is no match in pp at all. However, it will not do what you want when there is a match, but not for search.

user_11440350
Related