CODEWITHSUNDEEP
mysqlsql
Roy
Roy

Reputation: 61

Joining two tables in mysql - One to many relationship

I have 2 tables in mysql - User (user_id, first_name ....) and login_history(user_id, login_time)

Every time an user loges in, system records the time in login_history.

I want to run a query to fetch all the fields from the users table and the latest login time from login_history . Can anyone help please?

Upvotes: 2

Views: 71

Answers (3)

shrikant
shrikant

Reputation: 971

SELECT t1.col1
    ,t1.col2
    ,[...repeat for all columns in User table]
    ,max(t2.login_time)
FROM user t1
INNER JOIN login_history t2 ON t1.user_id = t2.user_id
GROUP BY t1.col1
    ,t1.col2
    ,[..repeat for all columns in User table]

This should work, assuming login_time is stored in a sane data type and/or format.

Upvotes: 1

Jignesh Adesara
Jignesh Adesara

Reputation: 31

Following are 2 queries that can help you out to select latest login time with user details

SELECT * FROM User C,login_history O where C.user_id=O.user_id order by O.login_time desc limit 1

or

SELECT * FROM User C,login_history O where C.user_id=O.user_id and ROWNUM <=1 order by O.login_time desc

Upvotes: 0

Latsuj
Latsuj

Reputation: 479

You have to use a join then :

SELECT *, login_history.login_time
FROM User
INNER JOIN login_history
ON User.user_id=login_history.user_id;

And this query gonna give you, all the columns of User and the login_time.

Upvotes: 1

Related Questions