Geek Answers Handbook

SQL: join of three tables - combined inner / left outer join?

having a bit of trouble visualizing how the connection should look for the specific result set that I am trying to achieve.

I have three tables: Projects, Recommendations, Services. Recommendations - this is just a table of connections between projects and services, that is, a project can have zero or more recommended services; To capture this relationship, the recommendation table has project_id and service_id for each recommendation record.

So the corresponding fields are:

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

I am trying to list all projects for which there are no recommendations for a particular service. I have the following that pulls all projects for which there are no recommendations, and this:

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

I know that I also need to join the Services table, but I'm not sure how to structure it. Any advice is appreciated.

-1
source share
2 answers
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 a service identifier. Note. I took out clure R.project_id IS NULL. You can also use a subquery (which is likely to be faster, but check 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 will not want to use select * ever. Place the required fields in the selected part.

+2
source

One parameter is a subquery, without any connection:

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

More articles:


All Articles