MYSQL help need for joining 2 tables with multiple results

Question

Using the 2 tables below i would like to show a list of names with the next available time showing next to their name. Only 1 result per name. Can you help? Thanks Bobby

user table
|  user_ID  |  name   
|  1        |   Smith  
|  2        |   Jones 

times_available
|    ID    |   user_ID  | time_avail |
|        1 |   2        |   01:30    |
|        2 |   2        |   02:30    |
|        3 |   3        |   02:30    |
|        4 |   3        |   03:30    |
|        5 |   3        |   04:30    |

Display next available time for each name

|  user_ID  |  time   |
|    Smith  |   02:30 |
|    Jones  |   02:30 |

@Clodoaldo has a good answer, but I need to also show the names that do not have upcoming time_avail

@AarolamaBluenk since this question shows no effort.. would you solve it? — Bobby, Feb 27 '13 at 23:06

Clodoaldo Neto · Accepted Answer · 2013-02-27T18:41:51.837

2

select u.name, min(t.time_avail)
from
    user_table u
    inner join
    times_available t on u.user_id = t.user_id
where t.time_avail > current_time
group by u.user_id

edited Feb 27 '13 at 18:41

answered Feb 27 '13 at 18:25

Clodoaldo Neto

118,695
26
233
260

this statement shows the same time for ever name – Bobby Feb 27 '13 at 18:39
Thanks!!!! You answered my first of many questions. The best part was your answer was simple and to the point. – Bobby Feb 27 '13 at 18:53
@ClodoaldoNeto What if I still want to show the rest of the names that do not have an upcoming time? – Bobby Feb 27 '13 at 19:15

Muhammad Raheel · Answer 2 · 2013-02-27T18:51:05.077

0

You can do it like this

SELECT 
    u.user_ID,
    u.name,
    ta.NewTime
FROM user AS u
LEFT JOIN (
       SELECT r.* , lta.time_avail AS `NewTime` FROM times_available as r
       LEFT JOIN (SELECT * FROM times_available LIMIT 1,69596585953484) AS lta
          ON lta.ID = (SELECT MIN(ID) FROM times_available WHERE ID > r.ID GROUP BY r.ID limit 1)
) AS ta ON ta.user_ID = u.user_ID 
WHERE ta.NewTime IS NOT NULL
GROUP BY u.user_ID

Fiddle Demo

edited Feb 27 '13 at 18:51

answered Feb 27 '13 at 18:28

Muhammad Raheel

19,823
7
67
103

1

`LIMIT 1,69596585953484` what is the point of this? – Kermit Feb 27 '13 at 18:33
Join on the same table avoid first one and join second row to first row. creating a demo! – Muhammad Raheel Feb 27 '13 at 18:35
@AarolamaBluenk look at my answers [this](http://stackoverflow.com/questions/15083296/mysql-select-rank-of-user-more-than-x-less-than-y/15083666#15083666) and [this](http://stackoverflow.com/questions/11262260/finding-free-blocks-of-time-in-mysql-and-php) – Muhammad Raheel Feb 27 '13 at 20:43

MYSQL help need for joining 2 tables with multiple results

2 Answers2