CODEWITHSUNDEEP
mysql
stand
stand

Reputation: 139

Not all strings from left join

I have this query

select delivery_first_week_repo.type, delivery_first_week_repo.from_sale, delivery_week_repo.from_sale
from delivery_first_week_repo
LEFT JOIN  delivery_week_repo on delivery_first_week_repo.type = delivery_week_repo.type
where delivery_first_week_repo.week_day = 'Tuesday'
and delivery_week_repo.week_day = 'Tuesday'

In delivery_first_week_repo there are some strings with type which is not in second table. But they are not displayed upon request.

Upvotes: 0

Views: 24

Answers (2)

Barmar
Barmar

Reputation: 781068

When there's no matching row in the second table, the columns from the second table will be null in the join. So delivery_week_repo.week_day = 'Tuesday' will never be true for those rows.

Conditions on the second table should be put into the ON clause, not the WHERE clause.

select delivery_first_week_repo.type, delivery_first_week_repo.from_sale, delivery_week_repo.from_sale
from delivery_first_week_repo
LEFT JOIN  delivery_week_repo on delivery_first_week_repo.type = delivery_week_repo.type
    and delivery_week_repo.week_day = 'Tuesday'
where delivery_first_week_repo.week_day = 'Tuesday'

Upvotes: 1

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

That is because of the LEFT JOIN type.

using inner join will goive you the matching results

select delivery_first_week_repo.type, 
delivery_first_week_repo.from_sale, delivery_week_repo.from_sale
from delivery_first_week_repo
INNER JOIN  delivery_week_repo on delivery_first_week_repo.type = 
delivery_week_repo.type
where delivery_first_week_repo.week_day = 'Tuesday'
and delivery_week_repo.week_day = 'Tuesday'

Upvotes: 0

Related Questions