Select rows that are not in the link table.

Question

I have 3 tables. One of them is a table of services. Each service may have some parameters that are in the options table. The third table is a list of contacts associated with each option.

So the tables look like this:

services

serviceID 100 101 

options

 optionID serviceID price 1 100 10.5 2 100 100 3 101 25.25 4 101 67.90 

contactOptions

 contactID optionID serviceID priceOverride 10 1 100 NULL 10 2 100 NULL 10 3 101 30 10 4 101 100 11 1 100 12 11 3 101 NULL 12 2 100 55 12 3 101 NULL 12 4 101 NULL 13 1 100 NULL 13 2 100 66 13 4 101 NULL 

As you can see, contactID 10 has all 4 options associated with it. 11 has only 2 associated with it, and 12 and 13 both have only 3.

I am trying to find a way to get contactIDs that don't have all 4 parameters and which ones are missing.

I tried (SQL Fiddle: http://sqlfiddle.com/#!2/ca937/7 ):

 SELECT * FROM contactOptions LEFT JOIN options ON options.optionID = contactOptions.optionID AND options.serviceID = contactOptions.serviceID 

but that just returns me all 12 lines, I want the other way around, lines that don't exist.

How can I return contacts that do not have all 4 parameters, and parameters that they do not have?

The returned rows should be:

 contactID optionID serviceID 11 2 100 11 4 101 12 1 100 13 3 101 

Ultimately, I want to find out which services (and which of its options) do not have contacts associated with them.

EDIT : I was approaching, that's what I am now

 SELECT contactID, contactOptions.serviceID,contactOptions.optionID AS x, options.optionID AS y FROM contactOptions JOIN options ON options.serviceID = contactOptions.serviceID HAVING x != y 

Here is my current SQL script: http://sqlfiddle.com/#!2/ca937/35

This returns:

 CONTACTID SERVICEID XY 10 100 1 2 10 100 2 1 10 101 3 4 10 101 4 3 11 100 1 2 11 101 3 4 12 100 2 1 12 101 3 4 12 101 4 3 13 100 1 2 13 100 2 1 13 101 4 3 

This contains strings that I want, but also contains strings that I don't have. From this result, I basically need to delete all the lines where the contact has the same values ​​for X and Y, then the remaining lines are correct.

EDIT 2 : now I'm even closer ( http://sqlfiddle.com/#!2/ca937/53 ):

 SELECT contactID, contactOptions.serviceID,contactOptions.optionID AS x, options.optionID AS y FROM contactOptions JOIN options ON options.serviceID = contactOptions.serviceID GROUP BY contactID,serviceID HAVING x != y 

This gives me:

 CONTACTID SERVICEID XY 12 100 2 1 13 101 4 3 

Now I have two lines that I want. I just need to figure out how to get the rows for 11.

Answer 1

 SELECT contacts_options_full.contactID, contacts_options_full.optionID, options.serviceID FROM ( SELECT contactID, optionID FROM (SELECT optionID FROM options) o CROSS JOIN (SELECT DISTINCT contactID FROM contactOptions) co ) contacts_options_full LEFT JOIN contactOptions ON contacts_options_full.contactID = contactOptions.contactID AND contactOptions.optionID = contacts_options_full.optionID JOIN options ON contacts_options_full.optionID = options.optionID WHERE contactOptions.serviceID IS NULL ORDER BY contacts_options_full.contactID, contacts_options_full.optionID 

on sqlfiddle

Answer 2

 SELECT *,COUNT(contactID)as cc FROM contactOptions GROUP BY contactID HAVING cc < 4 

Of course, to calculate all the parameters just do:

 SELECT COUNT(*) FROM options 

Edit for comments:

After you have all your contact name with the previous request, you can run this request for each of your contacts:

 SELECT optionID FROM options WHERE optionID NOT IN (SELECT optionID FROM contactOptions WHERE contactID = 11) 

Answer 3

I worked on this a bit more and I came up with my own answer.

 SELECT serviceContact.contactID, options.optionID, serviceContact.serviceID FROM options JOIN( SELECT DISTINCT contactID, services.serviceID, GROUP_CONCAT(optionID) as options FROM contactOptions JOIN services ON services.serviceID = contactOptions.serviceID GROUP BY contactID, services.serviceID ) serviceContact ON serviceContact.serviceID = options.serviceID WHERE NOT FIND_IN_SET(optionID,serviceContact.options) 

Here's the GROUP_CONCAT trick mixed in with NOT FIND_IN_SET . This allows me to get parameters not in the link table.

DEMO: http://sqlfiddle.com/#!2/ca937/87