MySQL INNER JOIN duplicates one column

Question

I've got a table user_log where I store information about user activity, for example login and logout actions. Table has four colums: id, user_id, action and timestamp

I would like to select all logout and login actions timestamps for some user_id in one result table, there would be columns like this:

user_id login_timestamp logout_timestamp.

I use this MySQL query:

SELECT 
    user_log.user_id,
    user_log_login.login_timestamp,
    user_log_logout.logout_timestamp
FROM
    user_log
INNER JOIN
    (SELECT 
        user_id, timestamp as login_timestamp
    FROM
        user_log
    WHERE
        action = 'login successful') as user_log_login
ON
    user_log.user_id = user_log_login.user_id
INNER JOIN
   (SELECT 
        user_id, timestamp as logout_timestamp
    FROM
        user_log
    WHERE
        action = 'logout successful') as user_log_logout
ON
    user_log.user_id = user_log_logout.user_id
WHERE
    user_log.user_id = 4

But the result contains duplicates of login_timestamp. Have a look:

Yes because you have more than one logout_timestamp for user_id = 4. — Pரதீப்
– Pரதீப், Commented Mar 6, 2016 at 9:58
@MotoGP I have the same amount of login and logout timestamps. Select Distinct using with () gives me syntax error, but without brackets I've got the same issue as posted. — pavon147
– pavon147, Commented Mar 6, 2016 at 10:03
i think you would get a cross joined result for login and logout timestamp for a given user. You need to add more filter conditions. — Vamsi Prabhala
– Vamsi Prabhala, Commented Mar 6, 2016 at 10:03

sagi · Accepted Answer · 2016-03-06 10:06:08Z

1

I can suggest another approach, a correlated query in the select :

SELECT t.user_id,t.timestamp Login_Time ,
       (SELECT s.timestamp FROM user_log s
        WHERE s.user_id = t.user_id and s.action = 'logout successful'
            AND s.timestamp > t.timestamp
        ORDER BY s.timestamp limit 1) as Logout_Time
FROM user_log t
WHERE t.user_id = 4 
    AND t.action = 'login successful'

This will basically select the next logout for each login and will be faster then selecting from the table 3 times.

answered Mar 6, 2016 at 10:06

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL INNER JOIN duplicates one column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related