multiple subquery result into main query, select in select

Question

OK have 2 tables

user_id  login_history
1        2011-01-01
1        2011-01-02
1        2011-03-05
1        2011-04-05
1        2011-06-07
2        2011-01-01
2        2011-01-02
3        2011-03-05
3        2011-04-05
3        2011-06-07


user_id  user_details
1        Jack
2        Jeff
3        Irin

What kind of query can I use to get a result like

1. Jack 2011-01-01 2011-01-02 2011-03-05
2. Jeff 2011-01-01 2011-01-02 
3. Irin 2011-03-05 2011-04-05 2011-06-07

Basically I want latest 3 records from table one and be joint with table 2

The query I used will get me a list of below, which is vertical records

Jack ,2011-01-01
Jack ,2011-01-02
Jack ,2011-03-05

Jeff ,2011-01-01
Jeff ,2011-01-02

Irin ,2011-03-05
Irin ,2011-04-05
Irin ,2011-06-07

Please help

Nicola Cossu · Accepted Answer · 2011-11-28 23:28:47Z

1

select t2.user_details,
substring_index(group_concat(login_history order by login_history separator ' '),' ',3) as recents
from table_2 as t2
left join table_1 as t1
on t1.user_id = t2.user_id
group by t2.user_id

in your example you list first three records, not the last three. By the way you would have just to add desc to the order clause within group_concat if you need it.

edited Nov 28, 2011 at 23:28

answered Nov 28, 2011 at 23:14

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

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

1 Comment

Mike C Over a year ago

Thank you heaps ... This solves the puzzle ~~ This is a mysterie advanced function I've never heard of before :)

Collectives™ on Stack Overflow

multiple subquery result into main query, select in select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related