CODEWITHSUNDEEP
sqlms-access
Jelle Taal
Jelle Taal

Reputation: 25

SQL query doesn't show al the records

I want to show all the records from services regardles of if they excist in service_overview. But it only shows 3 records, that's becaus they are the only ones that aren't picked in any record in service_overview.

SELECT *
FROM services
LEFT JOIN service_overview ON service_overview.ServiceID = services.serviceID
WHERE service_overview.OverviewID IS NULL OR service_overview.OverviewID = 1
ORDER BY services.serviceID

This returns this As you can see this doesn't return all the records from services servicid's 1, 3, 4, 5.

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need to put the condition in the ON clause, not the WHERE clause:

SELECT *
FROM services s LEFT JOIN
     service_overview so
     ON so.ServiceID = s.serviceID AND so.OverviewID = 1
ORDER BY s.serviceID;

The problem with your query is that it filters out services that have an OverviewID other than 1 -- unless they also have 1.

Upvotes: 1

Related Questions