Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Compare 2 records on the same table

I have a user table that has multiple records for each user based on each year..

ID | Name  | MemberID
1  | Dave  | qwe/2022
2  | Sue   | asd/2022
2  | Jas   | zxc/2022
3  | Dave  | qwe/2023
4  | Jas   | zxc/2023
5  | Jas   | zxc/2024

I am trying to find the users that were here last year but haven’t registered this year.

E.g., I am expecting to see just Dave as he was here last year but not yet this year.

I have been working on…

SELECT A1.ID, A1.Name, A1.Year, B1.Year FROM `users` A1 
INNER JOIN `users` B1 ON A1.ID = B1.ID
WHERE
A1.Name LIKE = "%/2023" AND B1.Name NOT LIKE "%/2024"

I have been looking at this for so long, I can’t see the problem.

Tags:

4

Answers


  1. 0 
    SELECT * 
FROM
Users U
WHERE MemberId LIKE '%/2023' 
AND NOT EXISTS 
(SELECT 1 FROM Users N WHERE U.Name = N.Name AND N.MemberId LIKE '%/2024')

    Note that I haven’t tried running it. The idea is to get records which are present in 2023 but the same user doesn’t exist in 2024, assuming name is unique.

    Login or Signup to reply.
  2. 0

    I suggest you change to using a LEFT JOIN if you want to look for a missing record

    SELECT A1.ID, A1.Name, A1.Year, B1.Year
FROM `users` A1 
LEFT JOIN `users` B1 ON
    A1.Name = B1.Name
    AND B1.MemberID LIKE "%/2024"
WHERE A1.MemberID LIKE "%/2023"
AND B1.ID IS NULL
    Login or Signup to reply.
  3. 0 
    SELECT A1.ID, A1.Name, A1.Year, B1.Year 
FROM `users` A1 
LEFT JOIN `users` B1 ON A1.ID = B1.ID
WHERE right(A1.Name, 4) = "2023" 
  AND right(B1.Name, 4) = "2024"
  AND B1.ID is NULL

    Using Right() generally has better performance than LIKE

    Login or Signup to reply.
  4. 0

    This can be done using group by and having clauses, the condition is that there must be at least one record in 2023 and zero record in 2024 for each Name :

    select Name  
from mytable
group by Name
having count(case when MemberID LIKE "%/2023" then 1 end) >= 1
       and count(case when MemberID LIKE "%/2024" then 1 end) = 0;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search