MySQL: Many to Many Join Where Not Exists

Question

I'm posting this question again because I think I may have worded it poorly last time, and the solution I thought was working, isn't.

I have 3 tables: Projects, Services, and Recommendations. Recommendations provides the many-to-many relationship between Projects and Services, i.e. each row in Recommendations has a project_id and a service_id.

Let's say there are 1000 projects, and 5 services. I would expect no greater than 5000 records in my Recommendations table, but almost certainly fewer (i.e. some projects have no service recommendations). So, for project #1, if all 5 services have been recommended, I would see 5 rows in the Recommendations table like:

project_id   service_id
1            1
1            2
1            3
1            4
1            5

What I am trying to do is build a query that shows me which projects do NOT have all 5 services recommended, and which those are. So let's say project #1 only had the first 3 services recommended; the output of my query showing which ones are missing might look like:

project_id   service_id
1            4
1            5

Thanks!

Answer 1

Select P.project_id, S.service_id
From Projects As P
    Cross Join Services As S
Where Not Exists    (
                    Select 1
                    From Recommendations As R1
                    Where R1.project_id = P.project_id
                        And R1.service_id = S.service_id
                    )

Another variant which should work in MySQL

Select P.project_id, S.service_id
From Projects As P
    Cross Join Services As S
Where (P.project_id, S.service_id) Not In   (
                                            Select R1.project_Id, R1.service_id
                                            From Recommendations As R1
                                            )