CODEWITHSUNDEEP
mysqljoinmany-to-many
Gabriel Matusevich
Gabriel Matusevich

Reputation: 3855

SQL Select Many to Many

I'm using MYSQL 5.X and I'm having trouble figuring out a Query;

I have 2 entities with a many to many relationship.

client and service, therefore I have 3 tables:

clients, clients_has_services and services.

I need to Select EVERY Service that is NOT related to a particular client.

for example: I have client with ID 1, and 4 Services in Total (Service1, Service2, Service3, Service4), client 1 has a relationship with Service1) so I need to retrieve all Other services (Service2, Service3, Service4)

Suggestions?

Upvotes: 0

Views: 220

Answers (2)

Nick
Nick

Reputation: 766

Assuming that your client_has_services rectifies the many-to-many so you have one to many on both sides, so your client_has_services should have: ID (Key), Client_ID, Service_ID

SELECT * FROM services WHERE services.id != ANY (select client_has_services.services_id from client_has_services where client_has_services.client_id = ID_num_provided)

Upvotes: 1

spencer7593
spencer7593

Reputation: 108510

First, get a set all services.

Join that to the row from clients for the "particular client", so you have a set of all services for the client.

The "trick" is to use an anti-join pattern to exclude rows where you have "matches" in the clients_have_services table.

If you have the unique identifier for the client (and you only need the list for a single client), something like this:

SELECT s.*
  FROM services s
  LEFT
  JOIN clients_have_services h
    ON h.service_id = s.id
   AND h.client_id = 42
 WHERE h.service_id IS NULL
 ORDER BY s.id

The outer join returns all rows from services, along with any "matching" rows from the clients_have_services table. The "trick" is the predicate in the WHERE clause that excludes any rows where a match was found, leaving just the services that are not related to the particular client.

If you are doing this for multiple clients... you'd need to also return the client cross joined with services (as cross product of clients and services), and then exclude the matches.

For example:

SELECT c.id AS client_id
     , s.*
  FROM clients c
 CROSS 
  JOIN services s
  LEFT
  JOIN clients_have_services h
    ON h.service_id = s.id
   AND h.client_id = c.id
 WHERE h.service_id IS NULL
 ORDER BY c.id, s.id

There are a couple of other query patterns that will return an equivalent result, for example, a NOT EXISTS

SELECT s.*
  FROM services s
 WHERE NOT EXISTS 
       ( SELECT 1
           FROM clients_have_services h
          WHERE c.client_id = 42
            AND h.service_id = s.id
       )
 ORDER BY s.id

Upvotes: 1

Related Questions