# SQL query question: JOIN that discards multiple matches

**URL:** <https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974>\
**Category:** Factual Questions\
**Created:** [January 6, 2010, 11:21pm UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974 "2010-01-06T23:21:30Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![toadspittle](https://avatars.discourse-cdn.com/v4/letter/t/c0e974/32.png) [@toadspittle](https://boards.straightdope.com/u/toadspittle)\
**Post date:** [January 6, 2010, 11:21pm UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/1 "2010-01-06T23:21:30Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [January 7, 2010, 12:30am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/2 "2010-01-07T00:30:03Z")

</div>

Which flavor of SQL are you using?

---

<div class="post-metadata">

**Author:** ![toadspittle](https://avatars.discourse-cdn.com/v4/letter/t/c0e974/32.png) [@toadspittle](https://boards.straightdope.com/u/toadspittle)\
**Post date:** [January 7, 2010, 12:54am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/3 "2010-01-07T00:54:39Z")

</div>

MySQL.

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [January 7, 2010, 1:31am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/4 "2010-01-07T01:31:10Z")

</div>

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.

```auto

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:

```auto

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.

---

<div class="post-metadata">

**Author:** ![fandango](https://avatars.discourse-cdn.com/v4/letter/f/b5ac83/32.png) [@fandango](https://boards.straightdope.com/u/fandango)\
**Post date:** [January 7, 2010, 5:56pm UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/5 "2010-01-07T17:56:05Z")

</div>

Previous answer was good enough.

---

<div class="post-metadata">

**Author:** ![toadspittle](https://avatars.discourse-cdn.com/v4/letter/t/c0e974/32.png) [@toadspittle](https://boards.straightdope.com/u/toadspittle)\
**Post date:** [January 7, 2010, 8:08pm UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/6 "2010-01-07T20:08:19Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![toadspittle](https://avatars.discourse-cdn.com/v4/letter/t/c0e974/32.png) [@toadspittle](https://boards.straightdope.com/u/toadspittle)\
**Post date:** [January 7, 2010, 11:29pm UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/7 "2010-01-07T23:29:04Z")

</div>

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

```auto

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.

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [January 8, 2010, 1:21am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/8 "2010-01-08T01:21:09Z")

</div>

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.,

```auto

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 

```

---

<div class="post-metadata">

**Author:** ![toadspittle](https://avatars.discourse-cdn.com/v4/letter/t/c0e974/32.png) [@toadspittle](https://boards.straightdope.com/u/toadspittle)\
**Post date:** [January 8, 2010, 1:27am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/9 "2010-01-08T01:27:22Z")

</div>

Stellar! Many thanks.

---

<div class="post-metadata">

**Author:** ![Bobalude](https://avatars.discourse-cdn.com/v4/letter/b/5fc32e/32.png) [@Bobalude](https://boards.straightdope.com/u/Bobalude)\
**Post date:** [January 8, 2010, 4:06am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/10 "2010-01-08T04:06:18Z")

</div>

> [@Punoqllads](#):
>
> 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.,
> 
> ```auto
> 
> 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 
> 
> ```

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)

```php

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'

```

---

<div class="post-metadata">

**Author:** ![Bobalude](https://avatars.discourse-cdn.com/v4/letter/b/5fc32e/32.png) [@Bobalude](https://boards.straightdope.com/u/Bobalude)\
**Post date:** [January 8, 2010, 4:23am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/11 "2010-01-08T04:23:04Z")

</div>

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.

```php
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

```

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [January 8, 2010, 6:42am UTC](https://boards.straightdope.com/t/sql-query-question-join-that-discards-multiple-matches/523974/12 "2010-01-08T06:42:12Z")

</div>

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.