MySQL - SELECT columns according to their values

Question

I have an events table in my database, and each event can have up to 5 different dates. Date1 in the table is always before date2, date2 before date3 etc.

Given 2 dates in the search form, I'm trying to find the events between them.

My table is designed as follows:

id | name  | date1     | date2      | date3     | date4     | date5
1  | test1 | 2013-05-24| 2013-05-25 | 0000-00-00| 0000-00-00| 0000-00-00
2  | test2 | 2013-06-01| 2013-06-08 | 2013-06-15| 2013-06-23| 2013-06-30
3  | test3 | 2013-03-15| 0000-00-00 | 0000-00-00| 0000-00-00| 0000-00-00

$datefrom , $dateto are the two variables from the search form. Now the $datefrom for example, will always search in date1 of the table. But $dateto has to search in date5, and if it is null to date4 and so on.

The best query I came up with so far is this:

SELECT * FROM events 
WHERE 
IF(date1 != '0000-00-00', IF(date1>='2012-12-19', 1, 0),0) = 1 
AND CASE 
WHEN date5!='0000-00-00' THEN IF(date5<='2012-12-31', 1, 0)     
WHEN date4!='0000-00-00' THEN IF(date4<='2012-12-31', 1, 0)     
WHEN date3!='0000-00-00' THEN IF(date3<='2012-12-31', 1, 0) 
WHEN date2!='0000-00-00' THEN IF(date2<='2012-12-31', 1, 0) 
END

But it is not working very well, for example this query returns rows with its oldest date being in 2013. I don't know if CASE is the right approach to begin with. Any ideas?? Thanks for your time!

I don't really understand the concept: you have random dates for each event - why should the data should make sense, when the events don't have any relation to each other. Or are you handling each and every event request separately? — vlex
– vlex, Commented Dec 22, 2012 at 15:54
Can you show the expected results based on above sample data table? — bonCodigo
– bonCodigo, Commented Dec 22, 2012 at 16:03

echo_Me · Accepted Answer · 2012-12-22 16:55:12Z

2

you can use it so simple with GREATEST

SELECT name , date1 date_start, GREATEST(date2,date3,date4,date5) date_end
FROM events

and will output like that

 NAME   DATE_START  DATE_END
 jack   2013-05-24  2013-05-25
 peter  2013-06-01  2013-06-30

here THE DEMO SQLFIDDLE

answered Dec 22, 2012 at 16:55

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

5 Comments

FredTheWebGuy Over a year ago

Thank you thank you thank you! Not so much for the answer, but for exposing me to sqlfiddle.... heard of jsfiddle but not this one... now I can fiddle with sql.... weeeee!

echo_Me Over a year ago

@DudeSolutions nice that it help you this answer :) ,

George G. Over a year ago

Thank you so very much for this! I didn't even know about GREATEST. MySQL keeps amazing me!

George G. Over a year ago

I'm sorry but I'm new to stackoverflow. How do I choose your answer as the correct one??

echo_Me Over a year ago

nice that it helped you , good luck with the rest of your work :).

Mahmoud Gamal · Accepted Answer · 2012-12-22 16:27:33Z

Try this:

SELECT 
  id, 
  name
FROM
(
    SELECT 
      id, 
      name, 
      MIN(EventDate) AS "FromDate",
      MAX(EventDate) AS "ToDate"
    FROM
    (
      SELECT id, name, date1 As "EventDate" FROM events
      UNION ALL
      SELECT id, name, date2                FROM events
      UNION ALL
      SELECT id, name, date3                FROM events
      UNION ALL
      SELECT id, name, date4                FROM events
      UNION ALL
      SELECT id, name, date5                FROM events
    ) t 
    GROUP BY id, name
) t
WHERE FromDate > @fromdate
  AND ToDate   < @todate;

SQL Fiddle Demo

However, if you want to get the data in the same way as it is in the events table, you can do this:

SELECT 
  e.*
FROM
(
    SELECT 
      id, 
      name, 
      MIN(EventDate) AS "FromDate",
      MAX(EventDate) AS "ToDate"
    FROM
    (
      SELECT id, name, date1 As "EventDate" FROM events
      UNION ALL
      SELECT id, name, date2                FROM events
      UNION ALL
      SELECT id, name, date3                FROM events
      UNION ALL
      SELECT id, name, date4                FROM events
      UNION ALL
      SELECT id, name, date5                FROM events
    ) t 
    GROUP BY id, name
) t
INNER JOIN events e ON t.id = e.id
WHERE t.FromDate > @fromdate
  AND t.ToDate   < @todate;

For instance, for @fromdate = '2012-05-01' and @todate = '2012-06-15', this will give you:

| ID |  NAME |                      DATE1 |                      DATE2 |  DATE3 |  DATE4 |  DATE5 |
---------------------------------------------------------------------------------------------------
|  1 | test1 | May, 24 2013 00:00:00+0000 | May, 25 2013 00:00:00+0000 | (null) | (null) | (null) |

Collectives™ on Stack Overflow

MySQL - SELECT columns according to their values

2 Answers 2

5 Comments

SQL Fiddle Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related