How to order/limit an inner join in MySQL?

Question

I am bad at SQL. I need to get certain information out of my database. This information is a list of User ID's and their username under a single account. There are 3 tables involved.

CUSTOMER

• customer_id

CUSTOMER_RELATIONSHIP

• relationship_type
• child_id
• parent_id

USER_NAME

• user_name -- String value of username
• customer_id -- foreign key
• start_stamp -- timestamp
• end_stamp -- timestamp

My USER_NAME database has many entries in it because it is possible to change a username. The start_stamp is when user_name has been activated, end_stamp is when it has ended. Usually, on a user that is open, there is an entry in the database where end_stamp is null (because user_name is currently active). However, if a user is closed, USER_NAME.end_stamp may not be null because the username is recently retired. Initially I was running a query where end_stamp is null, but I was not seeing the closed users under my account. I was wondering if there is a way to return the most recent user_name from my user_name table ( and only returning one ). Here is my query:

select distinct
    users.customer_id,
    accounts.customer_id,
    USER_NAME.user_name
from CUSTOMER users
    inner join CUSTOMER_RELATIONSHIP cr on cr.child_id = users.customer_id
        and cr.relationship_type = 1 -- Account/User relationship
    inner join CUSTOMER accounts on accounts.customer_id = cr.parent_id
        and accounts.customer_id = 25 -- given ID
    inner join USER_NAME on USER_NAME.customer_id = users.customer_id
        and USER_NAME.end_stamp is null

This is not returning usernames for closed users. If I remove the "and USER_NAME.end_stamp is null" it will return data I do not want ( usernames that are expired ). I do, however, want to be able to see the most recently associated user_name for a closed user.

How can I write a query that will order that USER_NAME inner join and get the entry with either 1) a null end_stamp or 2) the most recent start_stamp?

Answer 1

I would suggest a correlated subquery:

select c.*,
       (select un.user_name
        from user_name un
        where un.customer_id = c.customer_id
        order by (end_stamp is null) desc, end_stamp desc
        limit 1
       ) as most_recent_user_name
from customers c;