mysql nested IF statement in SELECT

Hi,

I have a problem getting a nested IF clause to work in my mysql SELECT statement.
Please can you have a look at the attached code particularly the line below which is not working
(IF spContract_start > (IF (start_date< '2011-04-04', '2011-04-04', start_date) as StartUnavail),spContract_start,StartUnavail) as start,

What should be happening is the follows

if start_date < '2011-04-04' use '2011-04-04' else use the value 'start_date'  - lets call ths used value StartUnavail

if spContract_start > StartUnavail calculated above use spContract_start else use StartUnavail.


Hope this makes sense. Thanks

 

SELECT tb2.start_date, tb2.end_date, tb2.hostel , tb2.room_no, tb4.project as project,
(IF spContract_start > (IF (start_date< '2011-04-04', '2011-04-04', start_date) as StartUnavail),spContract_start,StartUnavail) as start,
(IF (((end_date > '2011-07-03') OR (end_date IS NULL)),'2011-07-03',end_date) as EndUnavail)
FROM room_unavailable AS tb2 (LEFT OUTER JOIN room AS tb1 ON tb1.hostel = tb2.hostel AND tb1.room_no = tb2.room_no) CROSS JOIN hostel AS tb4 ON tb2.hostel = tb4.hostel_name
WHERE ((((tb2.start_date <= '2011-04-04') AND (tb2.end_date >='2011-04-04'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date >='2011-07-03'))
OR ((tb2.start_date >= '2011-04-04') AND (tb2.end_date <='2011-07-03'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date = '0000-00-00'))));

Open in new window

EICTAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
select
X.start_date, X.end_date, X.hostel , X.room_no, X.project,
Case When X.spContract_start > X.StartUnavail then X.spContract_start else X.StartUnavail end case as start,
X.EndUnavail
FRom
(SELECT tb2.start_date, tb2.end_date, tb2.hostel , tb2.room_no, tb4.project as project,spContract_start,

Case When start_date< '2011-04-04' then '2011-04-04' else start_date end case as StartUnavail
,

Case When ((end_date > '2011-07-03') OR (end_date IS NULL)) then
'2011-07-03' else end_date as EndUnavail
FROM room_unavailable AS tb2 (LEFT OUTER JOIN room AS tb1 ON tb1.hostel = tb2.hostel AND tb1.room_no = tb2.room_no) CROSS JOIN hostel AS tb4 ON tb2.hostel = tb4.hostel_name
WHERE ((((tb2.start_date <= '2011-04-04') AND (tb2.end_date >='2011-04-04'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date >='2011-07-03'))
OR ((tb2.start_date >= '2011-04-04') AND (tb2.end_date <='2011-07-03'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date = '0000-00-00')))) ) X
0
 
Pratima PharandeCommented:
SELECT tb2.start_date, tb2.end_date, tb2.hostel , tb2.room_no, tb4.project as project,

Case When spContract_start >
(Case When start_date< '2011-04-04' then '2011-04-04' else start_date end case)
then spContract_start else StartUnavail end case as start,

Case When ((end_date > '2011-07-03') OR (end_date IS NULL)) then
'2011-07-03' else end_date as EndUnavail
FROM room_unavailable AS tb2 (LEFT OUTER JOIN room AS tb1 ON tb1.hostel = tb2.hostel AND tb1.room_no = tb2.room_no) CROSS JOIN hostel AS tb4 ON tb2.hostel = tb4.hostel_name
WHERE ((((tb2.start_date <= '2011-04-04') AND (tb2.end_date >='2011-04-04'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date >='2011-07-03'))
OR ((tb2.start_date >= '2011-04-04') AND (tb2.end_date <='2011-07-03'))
OR ((tb2.start_date <= '2011-07-03') AND (tb2.end_date = '0000-00-00'))));
0
 
EICTAuthor Commented:
Thanks.
A quick question. StartUnavail should be the value of the nested case. The following seems a bit long (i.e. repeating the nested case).

Case When spContract_start >
(Case When start_date< '2011-04-04' then '2011-04-04' else start_date end case)
then spContract_start else (Case When start_date< '2011-04-04' then '2011-04-04' else start_date end case)
 end case as start,

if we called the nested case result A then it would look like this.

Case When spContract_start > (A) then spContract_start else (A) end case as start,



0
 
EICTAuthor Commented:
Thanks. Good idea to do a select within a select.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.