mysql case statement

Question

can i use 'AND' in the mysql case statement

for example:

$d = $mysqli->query("SELECT id_timeslot, date_book, id_beautician

     ,coalesce(thera_b, 'available') AS thera_b

    FROM ( SELECT t.id_timeslot, p.id_beautician, b.date_book

    , MAX(CASE b.id_beautician WHEN 2 THEN 'booked' THEN 'booked' ELSE NULL END 
       AND 
       CASE b.date_book WHEN '2011-01-04' THEN 'booked' ELSE NULL END) AS thera_b

    FROM timeslot as t
    LEFT JOIN bookslot as b ON b.id_timeslot = t.id_timeslot
    LEFT JOIN beautician as p ON p.id_beautician = b.id_beautician
        GROUP BY t.id_timeslot) AS xx");

or any other solution?

score 3 · Accepted Answer · answered Jan 04 '11 at 16:45

3

Is this what you need?

MAX(CASE WHEN b.id_beautician =2 
   AND b.date_book = '2011-01-04' THEN 'booked' END )  AS thera_b

This uses the searched case format (second one in the docs) and leaves out the ELSE NULL as this is implicit anyway.

answered Jan 04 '11 at 16:45

Martin Smith

438,706
87
741
845

You need to explain what you are trying to do then. Giving source code that doesn't work without explanation and leaving us to guess your intent probably isn't the most productive approach! – Martin Smith Jan 04 '11 at 16:52
MAX(CASE WHEN b.id_beautician=2 AND b.date_book = '$search_date' THEN 'booked' END) AS thera_b – tonoslfx Jan 04 '11 at 16:53
sorry my bad! this is from my previous question http://stackoverflow.com/questions/4512784/php-booking-timeslot. – tonoslfx Jan 04 '11 at 16:55
Doesn't help me at least. Can you add sample data and example desired result to your question. – Martin Smith Jan 04 '11 at 17:07
1

it sorted :) .. i missed comma in the code lol.. :D thanks m8 ;) – tonoslfx Jan 04 '11 at 17:09

mysql case statement

1 Answers1