MySql complex query from two tables

Question

I have two tables "USERS" and "CALENDER", in calender table user place his availability dates, mean 1-to-many relationship.

Now i want to get user list by availability date (today date mean available for today), i.e: i want to show those user who are available today on the top, then those user who's availability in future and then show those user who haven't any availability (i.e: no record in calender).

I am trying with below query:

SELECT u.first_name, c.date
FROM  users AS u
LEFT JOIN calendar AS c ON (c.uid = u.id)
GROUP BY u.id
ORDER BY DATE(c.date) ASC

but this will give me record by start by those user who have not any availability in calender and then those who have availability but in past. The results are given below:

User1   NULL                                          User13    2012-12-12 00:00:00
User2   NULL                                          User12    2012-12-12 00:00:00
User3   NULL                                          User10    2012-12-23 00:00:00                      
User4   NULL                                          User10    2012-12-24 00:00:00
User5   NULL                                          User9     2012-12-25 00:00:00
User6   NULL                 - Want To look this =>   User8     2012-12-27 00:00:00
User7   2012-09-20 00:00:00                           User7     2012-12-28 00:00:00
User8   2012-09-29 00:00:00                           User1     NUL
User9   2012-10-25 00:00:00                           User2     NUL
User10  2012-10-26 00:00:00                           User3     NUL
User11  2012-10-27 00:00:00                           User4     NUL
User12  2012-12-05 00:00:00                           User5     NUL
User13  2012-12-10 00:00:00                           User6     NUL

Daniel Schneller · Accepted Answer · 2012-12-12 07:29:02Z

1

Without having given this much thought, have you tried just reversing the sort order by using

... ORDER BY DATE(c.date) DESC

which will sort DESCending, instead of your explicit (and default) ASCending.

answered Dec 12, 2012 at 7:29

Daniel Schneller

14k5 gold badges46 silver badges72 bronze badges

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

1 Comment

ime.devdesks Over a year ago

sorry but you missed the point, what i want to count dates from today (means available) and then if some one user have tomorrow date and some other dates in future then we'll get the date in ASC form.

John Woo · Accepted Answer · 2012-12-12 07:38:28Z

1

modify your ORDER BY clause with

ORDER BY IF(DATE(c.date) IS NULL, 1, 0), DATE(c.date)

edited Dec 12, 2012 at 7:38

answered Dec 12, 2012 at 7:28

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

ime.devdesks Over a year ago

Hi John, i replace and get the result reverse Thanks. But what i want is yet not done. I want to get/count dates from today in ascending order, if someone have some past dates, those dates will be ignore.

sorencito · Accepted Answer · 2012-12-12 07:36:54Z

0

Use the IF function to map your null values to something else which will be sorted as you want, e.g. a date far in the past or even a string if that works for you.

http://www.java2s.com/Code/SQL/Function/MappingNULLValuestoOtherValuesforDisplay.htm

answered Dec 12, 2012 at 7:36

sorencito

2,6251 gold badge23 silver badges21 bronze badges

Comments

ime.devdesks · Accepted Answer · 2012-12-12 08:12:46Z

0

This problem can be solve by putting sub-query and get the future dates and then in ascending form will show exactly like in question, i.e: all available date record on the top then some future record and then show null value (if someone have no dates in calender).

SELECT u.first_name, 
 (
  SELECT DATE( c.date ) FROM calendar AS c
  WHERE c.uid = u.id
  AND DATE( c.date ) >= CURDATE( )
  ORDER BY DATE( c.date ) ASC
  LIMIT 1
) AS availability
FROM users AS u
GROUP BY u.id
ORDER BY IF( availability IS NULL , 1, 0 ) , availability

answered Dec 12, 2012 at 8:12

ime.devdesks

4043 gold badges5 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySql complex query from two tables

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related