CODEWITHSUNDEEP
mysql
Enigma Plus
Enigma Plus

Reputation: 1548

Selecting non-matching fields in mySQL

I have three tables as follows: Contact, Custom_Field, Custom_Field_Value.

Each contact can have one Custom_Field_Value record for each Custom_Field. So there is a 1:many relationship between Contact and Custom_Field_Value but it isn't quite that simple.

Everything works fine - except for one edge case where I need to select Contacts that have a particular Custom_Field not set (i.e. no corresponding Custom_Field_Value record exists linking to the Contact and the Custom_Field). This is surprisingly difficult. I can't just use the normal "left join and look for NULL" approach because they may have a different custom field - but not the one I am looking for. I need to say "Where Custom_Field_ID=10" but I can't because the thing I'm looking for does not exist.

My line of thinking was heading in this direction, but I'm just tying myself in knots now:

Select ID, First_Name, Last_Name, CF_ID  From 

(

(Select Contact.ID, First_Name, Last_Name, Custom_Field_Value.ID as CFV_ID, Custom_Field_Value.CustomFieldID as CF_ID, TextValue 
From Contact Inner Join Custom_Field_Value on Contact.ID = Custom_Field_Value.ContactID 
Where Custom_Field_Value.CustomFieldID=23 Order By Contact.ID)

UNION

(Select Contact.ID, First_Name, Last_Name, Custom_Field_Value.ID as CFV_ID, Custom_Field_Value.CustomFieldID as CF_ID, TextValue 
From Contact LEFT Join Custom_Field_Value on Contact.ID = Custom_Field_Value.ContactID 
Order by Contact.ID)

) as A


Group BY `ID`, CF_ID ASC

I don't want to create blank records for every possibility because there could be millions of records and every time someone adds a custom field, the database would have to insert millions of corresponding blank records.

It would be really great if we could do this:

Select ID From thingy 
EXCLUDE
Select * From thingy Where x = true

This is a nasty one, but I know there'll be someone out there who will love it:)

Upvotes: 1

Views: 113

Answers (2)

Enigma Plus
Enigma Plus

Reputation: 1548

This is the crazy SQL I ended up with - created dynamically by users. Just publishing it in case its any use to anyone. (Any tips on optimisation are very welcome!):

The problem is that not only do I have to select missing dynamic records, I also have to join together Left Join queries into one result.

SELECT * FROM (
    (SELECT * FROM Contact 
        WHERE (...some dynamic stuff...) 
    ) 
    UNION All 
   (SELECT Contact.* FROM Contact Inner Join Contact_Campaign_Link ON Contact.ID=Contact_Campaign_Link.Contact_ID 
       WHERE ((Campaign_ID=31))
   ) 
UNION All 
    (SELECT * FROM Contact 
        WHERE (CustomerID=3) 
        AND (NOT EXISTS 
            (SELECT * FROM Custom_Field_Value cfv2 
                WHERE (cfv2.ContactID = Contact.ID) 
                AND (cfv2.CustomFieldID =27) )) ORDER BY Contact.ID)
            ) As tbl 

GROUP BY tbl.ID HAVING COUNT(*)=3 Order by ID

Upvotes: 0

ESG
ESG

Reputation: 9435

Okay, I think I have a better understanding now. I was trying to pull it off without a subquery, but I'm not sure if I can.

Can you try

Select Contact.ID, First_Name, Last_Name, Custom_Field_Value.ID as CFV_ID, Custom_Field_Value.CustomFieldID as CF_ID, TextValue 
From Contact LEFT Join Custom_Field_Value on Contact.ID = Custom_Field_Value.ContactID 
WHERE NOT EXISTS(SELECT * FROM Custom_Field_Value cfv2 WHERE cfv2.ContactID = Contact.ID AND cfv2.CustomFieldID=23)
Order by Contact.ID

The NOT EXISTS subquery should only return rows where the contact has no value for that field.

Upvotes: 1

Related Questions