SQL query question: JOIN that discards multiple matches

Factual Questions
1

I have 2 tables in my database: USERS and SUBSCRIPTIONS.

Each row of subscriptions is keyed to the ID of each user. Thus, each user can have multiple subscriptions (either different products, or from different years), each with its own status (ACTIVE or EXPIRED) and BEGIN/END DATES.

Each year, these expired subscriptions pile up. I want to export a set of results that gives me only the most recent expired subscriptions for each user, and ignores the older ones. Any ideas on how I do this? I know how to get a set of ALL the expired subscriptions for a user, but not the most recent alone. (And I can’t rely on matching by this year/last year–the most recent expired subscription, in some cases, is several years ago.)

2

Which flavor of SQL are you using?

3

MySQL.

4

It’s been a while since I’ve used MySQL. I’m assuming that the table and column names are all-caps for a reason and that the MySQL you’re using is case-sensitive, and also assuming some names for relevant columns that you didn’t explicitly name. E.g., you didn’t say what the name of the column in a subscriptions table row that holds the id of the customer, so I named it USER_ID.



select s.USER_ID, s.PRODUCT_ID, s.BEGIN_DATE, s.END_DATE
from SUBSCRIPTIONS s
join
(
    select u.ID as USER_ID, s.PRODUCT_ID, max(s.END_DATE) as END_DATE
    from USERS u join SUBSCRIPTIONS s
    on u.ID = s.USER_ID and s.STATUS = 'EXPIRED'
    group by u.ID, s.PRODUCT_ID
) f
on s.USER_ID = f.USER_ID and s.PRODUCT_ID = f.PRODUCT_ID
 and s.END_DATE = f.END_DATE and s.STATUS = 'EXPIRED'

Note that you asked for the last expired subscription, not the most recent subscription. E.g., if a given user has an expired subscription from 3 years ago and a current active subscription to the same product, you’ll see the 3-year-old expired subscription. On the chance that you would rather have a query that gives you the most recent subscription if that subscription is expired, you would do:



select s.USER_ID, s.PRODUCT_ID, s.BEGIN_DATE, s.END_DATE
from SUBSCRIPTIONS s
join
(
    select u.ID as USER_ID, s.PRODUCT_ID, max(s.END_DATE) as END_DATE
    from USERS u join SUBSCRIPTIONS s
    on u.ID = s.USER_ID
    group by u.ID, s.PRODUCT_ID
) f
on s.USER_ID = f.USER_ID and s.PRODUCT_ID = f.PRODUCT_ID
 and s.END_DATE = f.END_DATE and s.STATUS = 'EXPIRED'

Note that this code assumes that a user won’t have both an active and an expired subscription with the same end date. If such an assumption shouldn’t be made, things get complicated.

Like I said, it’s been a while since I’ve touched MySQL so some of my syntax might be slightly off, no warranties expressed or implied, etc.

5

Previous answer was good enough.

6

Thanks, Punoqllads. Yes, I left many details out. (There are addl. columns that aren’t relevant, as well as a relevant column to deal with the later problem you point out: a column that records whether a subscription’s status is Active or Expired. So I can query just expired records.) But your inclusion of the MAX function in the first solution is key. Didn’t think about using that on a date value.

Thanks!

7

OK, more questions. Here’s the query I have now, which is working (I put in the actual, working table/field names):


SELECT s.`member_id` , s.`begin_date` , s.`expire_date`
FROM `amember_payments` AS s
JOIN (
SELECT u.`member_id` AS USER_ID, u.`status` AS USER_STATUS, MAX( s.`expire_date` ) AS END_DATE
FROM `amember_members` AS u
JOIN `amember_payments` AS s ON u.`member_id` = s.`member_id`
AND u.`status` = '2'
GROUP BY u.`member_id`
) AS f ON s.`member_id` = f.USER_ID
AND s.`expire_date` = f.END_DATE
AND f.USER_STATUS = '2'
LIMIT 0 , 200

This successfully outputs just the most recent expired subscription from users who have no active subscriptions whatsoever.

However, I still need to output more data from the amember_members table.

Thus, alongside each member_id , begin_date , expire_date (from the amember_payments table), I’d also like to show the following field values for that member_id from the amember_members table:

amember_members.login ,
amember_members.name_l ,
amember_members.name_f ,
amember_members.email ,
amember_members.street ,
amember_members.city ,
amember_members.state ,
amember_members.zip

Any guidance on this? I thought I should be able to throw them into the first SELECT statement, but I can’t seem to make it work, either with or without aliases.

8

To get other columns of amember_members, you’ve got two choices. You can put them both in the inner (as u.login, u.name_l, etc.) and outer select, (as f.login, f.name_l, etc.). Alternatively, you could also do another join on the amemeber_members table for the outside query. I recommend the first approach.

I.e.,


SELECT
  s.`member_id`, s.`begin_date`, s.`expire_date`, f.`login`, f.`name_l`,
  f.`name_f`, f.`email`, f.`street`, f.`city`, f.`state`, f.`zip`
FROM `amember_payments` AS s
JOIN (
  SELECT u.`member_id` AS USER_ID, u.`status` AS USER_STATUS, 
    MAX( s.`expire_date` ) AS END_DATE, u.`login`, u.`name_l`, u`.`name_f`,
    u.`email`, u.`street`, u.`city`, u.`state`, u.`zip`
  FROM `amember_members` AS u
  JOIN `amember_payments` AS s ON u.`member_id` = s.`member_id`
    AND u.`status` = '2'
  GROUP BY u.`member_id`
) AS f
ON s.`member_id` = f.USER_ID
  AND s.`expire_date` = f.END_DATE
  AND f.USER_STATUS = '2'
LIMIT 0 , 200
9

Stellar! Many thanks.

10

I work with DB2 and am not very familiar on the rules of mySQL, but are you required to include all items from your SELECT clause in the GROUP BY clause when using an aggregate (MAX) function?

Your sub-query only lists u.‘member_id’ in the GROUP BY but selects numerious things from the amember_members and 'amember_payments` table.

toadspittle:
I wrote this you can try assuming there is only one row on the amember_member table for every member_id (member_id never duplicated). And multiple member_ids are on the amember_payments table (multiple rows that show multiple subscriptions, each having a member_id value).

It wont work if there are multiple rows on the amember_member table with the same member_id (and different name/addresses on them). Would need an additional column to work if that is the case (to pair which amember_member address data matches the expired subscription).

(may have to modify the code to fit mySQL if something i wrote is DB2 specific)


WITH MEMBER_LIST AS
(
SELECT u.`member_id`,
	   MAX( s.`expire_date` ) AS expire_date
FROM `amember_members` AS u
WHERE u.`status` = '2'
GROUP BY u.`member_id`,
)

SELECT	s.`member_id`,
		s.`expire_date`,
		f.`login`,
		f.`name_l`,
		f.`name_f`,
		f.`email`,
		f.`street`,
		f.`city`,
		f.`state`,
		f.`zip`
FROM `MEMBER_LIST` AS s
INNER JOIN `amember_members` AS f
ON s.`member_id`, = f.'member_id'
11

ahh, I can’t edit my post anymore. You can try to use a row_number and partition by member_id, ordering the row_numbering by expire_date. Then just select from these results all the rows that equal 1.

Hopefully the inner sub-query here will return every single member_id with the member info, and then assign a 1 to the highest expire_date value, a 2 to the next highest, etc. The outer query just eliminates anything besides the highest expire_date.

I don’t have my resources from work with me, so it might not be working perfectly, but you can trying playing with the logic.

SELECT *
FROM
	(
	SELECT  s.`member_id`,
			s.`begin_date`,
			s.`expire_date`,
			u.`login`,
			u.`name_l`,
			u.`name_f`,
			u.`email`,
			u.`street`,
			u.`city`,
			u.`state`,
			u.`zip`,
			ROW_NUMBER() OVER(PARTITION BY s.`member_id` ORDER BY s.`expire_date` DESC) AS ROW_NBR
			
	FROM `amember_members` AS u

	INNER JOIN `amember_payments` AS s
	ON          u.`member_id` = s.`member_id`
	AND         u.`status` = '2'
	)
WHERE ROW_NBR = 1
12

My recollection of MySQL is that you didn’t have to do a group by on all of the columns, but the ones that you didn’t group by would be from an arbitrary row in the group, i.e., not necessarily the one with the latest end date. Since only one row in the user table will have a given user id, all of the user fields in a group will be identical, so that’s okay in this instance.