MySQL select a single record from highest join value from multiple tables with multiple records

Question

I have the following tables:

members

This stores a list of members for our system.

---------------------
| member_id | name  |
---------------------
| 1         | Bob   |
---------------------
| 2         | Joe   |
---------------------
| 3         | Tom   |
---------------------
| 4         | Bill  |
---------------------
| 5         | Will  |
---------------------

categories

This stores the categories for our system. Categories are not visible to members by default. A member must have a valid licence to be able to access a category (see below).

----------------------
| cat_id    | name   |
----------------------
| 1         | Cat1   |
----------------------
| 2         | Cat2   |
----------------------
| 3         | Cat3   |
----------------------

licences

Stores the licences that a member has. One member can have many licences. Licences can have a life time and will expire. Once a licence expires, the member can no longer view the category.

------------------------------------------------------
| id    | catid   | subid | valid_from  | valid_to   |
------------------------------------------------------
| 1     | 1       | 1     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 2     | 1       | 2     | 1999-01-01  | 2001-01-02 |
------------------------------------------------------
| 3     | 1       | 3     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 4     | 1       | 4     | 1999-01-01  | 2000-01-01 |
------------------------------------------------------
| 5     | 1       | 5     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 6     | 2       | 1     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 7     | 2       | 2     | 1999-01-01  | 2001-01-02 |
------------------------------------------------------
| 8     | 2       | 3     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 9     | 2       | 4     | 1999-01-01  | 2000-01-01 |
------------------------------------------------------
| 10    | 2       | 5     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 11    | 3       | 1     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------
| 12    | 3       | 2     | 2014-01-01  | 2020-12-01 |
------------------------------------------------------

preferences

The preferences table stores whether a member wishes to receive e-mails that are are sent relating to a category. The member can set a preference of '1' for 'wish to receive' or '0' for 'do not wish to receive'. A quirk is that if the member has no record (or a null value) we make an assumption that they wish to receive.

-----------------------------------
| id    | catid   | subid | pref  |
-----------------------------------
| 1     | 1       | 1     |  0    |
-----------------------------------
| 2     | 2       | 1     |  1    |
-----------------------------------
| 3     | 3       | 1     |  1    |
-----------------------------------
| 4     | 1       | 2     |  0    |
-----------------------------------
| 5     | 1       | 3     |  1    |
-----------------------------------
| 6     | 2       | 3     |  0    |
-----------------------------------

recipients

When an email is sent out based on a category, the recipient is logged so we don't email them more than once.

-----------------------------
| id    | emailid   | subid |
-----------------------------
| 1     | 1         | 1     |
-----------------------------
| 2     | 1         | 2     |
-----------------------------

I'm trying to write a query the fetches all members, and their related licence for a range of category IDs, their preferences and also make sure that they don't have a record in the recipients table.

In pseudo query:

SELECT [all members, their licence info, and preference setting]
FROM [members table]
WHERE [member doesnt exist in the recipients table for a given emailID]

The issue is that I need to check against multiple categoryIDs, but return just one result and only if the preference is set to 1 (or null, or doesn't exist).

So with the example data, given we are searching for categoryIDs 1,2 and 3 (A member must have a licence for at least one of these categories) and checking against emailID of 1, the only result should be for member_id 3 (Tom) with preference ID of 6 (because it's set to 1) and licence ID of 3 (because it's valid and the preference ID of 6 corresponds to it and it is set to 1). The second result should be member_id 5 (Will) as he has a licence to catids 1 and 2, he hasn't received the email with ID of 1 and he has no specific preference set.

Reason being: Members 1 and 2 are in the recipient table for emailID 1, member 2's licence has also expired, member 4's licence has expired and member 5 has their preference set to 0.

The query I've written which isn't working quite right is:

SELECT 
       members.member_id,
       members.name,
       licence.catid as licencedToCat,
       categories.cat_name as categoryName,
       licence.valid_from as licenceStart,
       licence.valid_to as licenceEnd,
       preferences.pref
FROM (`members`)
JOIN `licence` ON `licence`.`subid`=`members`.`member_id`
JOIN `preferences` ON `preferences`.`subid`=`members`.`member_id`
JOIN `categories` ON `categories`.`cat_id`=`licence`.`catid`
WHERE `licence`.`catid` IN (1,2,3)
   AND `start_date` <= '2014-12-16'
   AND `end_date` >= '2014-12-16'
   AND (pref='1' OR pref IS NULL)
   AND `members`.`member_id` NOT IN (SELECT subid FROM `recipients` WHERE `recipients`.`emailid`='1')
GROUP BY `licence`.`subid`

The issue is that the query is returning results saying users have a preference set to 1 where they actually don't even have a record set for that category.

The desired output is any member(s) along with the licence they have for the category but only if their preference for that category is 1/null/doesn't exist AND only if they don't appear in the recipients table for a given emailID.

So, if a member has 2 licences

I appreciate this was a long read, so thanks if you're still here! Any ideas on how to tweak my query to solve this?

Answer 1

I think part of your problem here is that you're using all inner joins. Like you said, a user may not have a preference, so a row may not be returned in your query. That being said, it seems like you want to inner join most tables, as it appears you only want members who have licenses, but you want to see all licenses regardless of whether that user has a preference. So, I made preferences an outer joined table:

SELECT m.*, l.catid AS licenseCat, c.name AS categoryName, 
  l.valid_from AS licenseStart, l.valid_to AS licenseEnd, p.pref AS preference
FROM members m
JOIN licenses l ON l.subid = m.member_id
JOIN categories c ON c.cat_id = l.catid
LEFT JOIN preferences p ON p.catid = c.cat_id AND p.subid = l.subid;

Once I had done that, I wrote the subquery that pulled the member_id of all members who are in the recipients table with the specified email:

SELECT subid
FROM recipients
WHERE emailid = 1;

Now you can insert that into your original query, and add your other requirements:

SELECT m.*, l.catid AS licenseCat, c.name AS categoryName, 
  l.valid_from AS licenseStart, l.valid_to AS licenseEnd, IFNULL(p.pref, 0) AS preference
FROM members m
JOIN licenses l ON l.subid = m.member_id
JOIN categories c ON c.cat_id = l.catid
LEFT JOIN preferences p ON p.catid = c.cat_id AND p.subid = l.subid
WHERE c.cat_id IN (1, 2, 3) AND
  l.valid_from <= '2014-12-06' AND l.valid_to >= '2014-12-06' AND
  m.member_id NOT IN (SELECT subid FROM recipients WHERE emailid = 1)
  AND (p.pref = 1 OR p.pref IS NULL);

You said in your question that this should return member_id 3 (which is Tom) but that does not match your results because member 5 has no preferences, so we should assume they want an email right? I'm also not sure how to group this for you. If a member has multiple subscriptions, which one do you want to keep?

I built an SQL Fiddle and tested what I have and it's really close. I hope this can at least push you in the right direction and I will edit the answer as needed.

EDIT

The following will give you what you want, but it is not always recommended. If you really don't care about the subscription dates (as long as it meets the criteria in the where clause) and you really don't care about the category for the user, just add GROUP BY m.member_id to get one row for each member.