Stumped on MySQL Query: How to select rows with a subset of values for a column?

Question

There are two tables, a Contact Table and a RelationshipHistory Table.
Contacts can have several types of relationships (Business, Individual, Volunteer, etc) which means they can have several references in the RelationshipHistory table over time. Each relationship row has a column indicating an event that the relationship is related to. So, for example, contact John Smith with ContactID 123 could have two rows in the RelationshipHistory Table like: 123, Individual, Fun Event and 123, Volunteer, Boring Event. The query that I need to perform is "Get all the contacts that have a relationship with event "Fun Event" and event "Boring Event".

I initially thought that a query like this would work:

SELECT DISTINCT LastName, FirstName 
  FROM Contacts 
  JOIN RelationshipHistory ON RelationshipHistory.ContactKey = Contacts.ContactGUID 
 WHERE RelationshipHistory.Event = 'Fun Event' 
   AND RelationshipHistory.Event = 'Boring Event'

But it doesn't return any results. When I took a second look at the query I realized that it is impossible because the comparison is happening on the rows and it doesn't make sense for a value in a column to equal two different values simultaneously.

The query seemed straightforward enough, but looking more closely at it, I'm not how to find the rows that have matching values on a column for two or more values.

Is there some way to use an inner select, or a join, or do I have to dump results into a temporary table...?

Answer 1

Use:

  SELECT LastName, FirstName 
    FROM Contacts c
    JOIN RelationshipHistory re ON re.ContactKey = c.ContactGUID 
                               AND re.Event IN ('Fun Event', 'Boring Event')
GROUP BY LastName, FirstName 
  HAVING COUNT(DISTINCT re.contactkey) = 2

Your query doesn't work because it expects the RELATIONSHIPHISTORY.contactkey to be both values, which is impossible. Which means you need to use OR, or use the IN clause which is shorthand for multiple ORs on the same column. However, the IN clause does not support any manner of pattern matching - you'd need to use Full Text Search (FTS) functionality for that...

The other part to this query is the HAVING clause - it counts the distinct contactkey values because if they weren't distinct, duplicates of "Fun Event" would be a false positive.

For the query to work, the number of IN clauses must match the number of distinct values being counted. So if you have three, or five events to look for, the HAVING clause needs to be updated.

Answer 2

If you need contacts which have relationship with both Fun Event and Boring Event:

SELECT DISTINCT LastName, FirstName 
  FROM Contacts 
  JOIN RelationshipHistory AS BoringEvent ON Boring.ContactKey = Contacts.ContactGUID 
  JOIN RelationshipHistory AS FunEvent ON Boring.ContactKey = Contacts.ContactGUID 
 WHERE FunEvent.Event = 'Fun Event' 
   AND BoringEvent.Event = 'Boring Event'

We are making two joins with the same table.