SQL: Join Three Tables - Combined Inner / Left Outer Join?

having a bit of trouble visualizing how a join should look for a particular result set I'm trying to achieve.

I have three tables: Projects, Recommendations, Services. Recommendations is just a join table between Projects and Services, i.e. a project can have zer开发者_开发问答o or more recommended services; to capture that relationship, the Recommendations table has a project_id and service_id for each recommendation record.

So, relevant fields:

Projects.id | Recommendations.project_id | Recommendations.service_id | Services.id

I'm trying to pull a list of all projects for which there is NO recommendation for a specific service. I have the following, which pulls all projects for which there are no recommendations at all, and that is:

SELECT * from projects P 
LEFT OUTER JOIN Recommendations R ON P.id = R.project_id 
WHERE R.project_id IS NULL

I know I need to join on the Services table as well, but I'm not sure how I should structure it. Any tips are appreciated.

SELECT P.* from projects P
LEFT JOIN Recommendations R 
    ON P.id = R.project_id 
LEFT JOIN  Services S 
    ON S.Service_id = R.service_id and s.ID = 10
WHERE s.service_id is null

This should find those records that do not have service id is 10. Note I took out where clause R.project_id IS NULL. You could also use the subquery approach (which is likely to be faster, but test both.)

SELECT * 
FROM projects 
WHERE NOT EXISTS (SELECT * 
                    FROM recommendations R
                    JOIN  Services S 
                        ON S.Service_id = R.service_id 
                    WHERE project_id=projects.id and s.ID = 10)  

Of course in real life you would not want to use select * ever. Please put the actual fields you want in the select portion.

One option is the subquery approach, without any join:

SELECT * FROM projects WHERE NOT EXISTS (SELECT * FROM recommendations WHERE project_id=projects.id AND recommendations.service_id=10)