SQL LEFT JOIN Not returning the correct DATA

Question

Im tryoing to build a SQL join table, but it is not returning the correct data that i want. So i have a sliders table with structure like below.

So the table has a foreign key that referes to an item. Items or sliders have a same column called: published, that tells if a sliders/items is or is't published.

I'm trying to build a query that returns all sliders that are published AND items where they belonge are published, Joined with the sliders where item_id is nll.

So according to the image above the query should return only 3 rows with id (2-3-4), because item with id 34 (item_id=34) on table items is not published therefore it shouldnt be on the returned value. The query that i have build so far is this:

SELECT *
FROM sliders
LEFT JOIN items ON sliders.item_id = items.id
WHERE sliders.published =1 AND items.published =1

But the query returns only 2 rows. rows with id 2 and 3 which correspond to item with id 23, 28.

The returned result is as follow:

So it returned 2 rows instead of 3, the row missing is the row with id 3, where item_id=null.

I have been trying all day to figure this out but it seems i am stuck here...

Any help would be very appreciated..

Answer 1

If you use a LEFT JOIN and there isn't a match, the right hand table (items) will have NULL as the values of its columns. You therefore can't have items.published = 1 in your WHERE clause and expect those rows to show up because NULL doesn't equal 1.

Assuming I've not misunderstood the question, you'll need to change that to AND (items.published IS NULL OR items.published = 1) - you could probably also get away with just doing AND items.published <> 0. That essentially checks that the items are published if they exist.