Good tutorial for CASE? 1

[Sleidia]

Hi,

Can't find a good tutorial about the CASE function.
Google isn't my friend anymore

 

[r937]

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

r937.com | rudy.ca

 

[Sleidia]

You must be kidding?

What I need is a good tutorial with lots of practical examples of queries.

It's after I saw this MISERABLE official documentation that I posted here.

 

[r937]

nope, wasn't kidding

i guess the mysql docs are an acquired taste, like snails or sushi, some people just can't ever get to like them

you sound like an intelligent guy, so i hesitate to suggest that you google for what you want...

try a couple o' these (remember, as far as syntax is concerned, all databases implement CASE similarly) --

http://www.4guysfromrolla.com/webtech/102704-1.shtml

http://sqlzoo.net/fun_case

http://www.craigsmullins.com/ssu_0899.htm

http://search400.techtarget.com/tip/0,289483,sid3_gci820138,00.html

http://dmxzone.com/go?6819

(modest fee required)

r937.com | rudy.ca

 

[Sleidia]

I see.
I googled with "mysql" instead of just "sql"

As for the documentation, it's not about taste.
The explanation dedicated to CASE is way shorter that any link you have found. They show you the basic syntax and then you're on your own.

 

[guelphdad]

I googled with "mysql" instead of just "sql"

remember that mysql is just one "flavour" of sql. personally it is why I stay away from proprietary items and stick to the standard SQL, for the most part they can be easily used in other databases.

 

[Sleidia]

Yay! I've finally writen my first successful CAUSE statement!

SELECT table_ID, service_type_ID, service_order, 
        CASE 
        WHEN service_type_ID IS NULL THEN CONCAT(table_ID, 0)
	ELSE CONCAT(service_type_ID, service_order)
        END AS final_order
FROM mod_hotel_services
ORDER BY final_order

(

http://www.tek-tips.com/viewthread.cfm?qid=1391023&page=1

)

BTW, r937, you're a liar!
I've been to Toronto and I swear that it's not true that "the weather allows you to play frisbee golf all year long."
Brrr what a dull and freezing place it was ...

 

[r937]

obviously you regard frisbee golf as a fair-weather-only sport?



r937.com | rudy.ca

 

[Sleidia]

The only living creatures I saw outside were frozen squirels.
I prefer much sexier spectators when I play
And no skirt = no fun.

 

[Sleidia]

Hmmm I don't get it.
Why can you use a constructed column on an ORDER clause but not on a WHERE clause???

For example, this doesn't work :

SELECT
table_ID, service_type_ID, service_order,

        CASE
        WHEN service_type_ID IS NULL THEN CONCAT(table_ID, 0)
	ELSE CONCAT(service_type_ID, service_order)
        END AS final_order,

        CASE
        WHEN service_type_ID IS NULL THEN table_ID
	ELSE service_type_ID
        END AS final_where

FROM
mod_hotel_services
WHERE
final_where = "1"
ORDER BY
final_order

 

[r937]

how come you can't? because you can't!



the reason has to do with the sequence in which the clauses are executed:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

r937.com | rudy.ca

 

[Sleidia]

Did it ever happen that you admit the mysql team design things the wrong way?

I'm sure you'll find a good reason (one that I can't understand) for the weird order in which the clauses are executed butin the end, it doesn't do something that should be feasible.

Well, I guess nothing can be perfect ( just like Toronto )

 

[r937]

if you want to use the column aliases in the WHERE clause, you have to do this:

[blue]SELECT *
  FROM ([/blue]
       SELECT table_ID
            , service_type_ID
            , service_order
            , CASE
                WHEN service_type_ID IS NULL 
                THEN CONCAT(table_ID, 0)
                ELSE CONCAT(service_type_ID, service_order)
               END AS final_order
            , CASE
                WHEN service_type_ID IS NULL 
                THEN table_ID
                ELSE service_type_ID
               END AS final_where
         FROM mod_hotel_services
       [blue]) as data
 WHERE final_where = 1
ORDER 
    BY final_order[/blue]

one of the very few instances where i think it's okay to use "select star"

by the way, your second CASE can be simplified as follows:

COALESCE(service_type_ID,table_type_ID) as final_where

r937.com | rudy.ca

 

[Sleidia]

Wow ... that's the weirdest query I've seen so far!
It's so counter intuitive!

 

[Sleidia]

I have another problem

The same table again :

+----------+-----------------+----------------------------------+---------------
| table_ID | service_type_ID | service_type_name                | service_order
+----------+-----------------+----------------------------------+---------------
|        1 |            NULL | service 1                        | 1
|        2 |            NULL | service 2                        | 2
|        4 |               1 | NULL                             | 1
|        5 |               1 | NULL                             | 2
|        6 |               1 | NULL                             | 3
|        7 |               2 | NULL                             | 1
|        8 |               2 | NULL                             | 2
+----------+-----------------+----------------------------------+---------------

What if I want to display the rows of table_ID IN (5,6) plus the row that has table_ID equal to the service_type_ID of 5 and 6 by using CASE directly INSIDE the where clause? Is it possible?

SELECT table_ID, service_type_ID, service_order,

     CASE
     WHEN service_type_ID IS NULL THEN CONCAT(table_ID, 0)
     ELSE CONCAT(service_type_ID, service_order)
     END AS final_order

FROM mod_hotel_services
WHERE

     CASE ... ??? 
     END

ORDER BY final_order

The result is then supposed to be :

+----------+-----------------+----------------------------------+---------------
| table_ID | service_type_ID | service_type_name                | service_order
+----------+-----------------+----------------------------------+---------------
|        1 |            NULL | service 1                        | 1
|        5 |               1 | NULL                             | 2
|        6 |               1 | NULL                             | 3
+----------+-----------------+----------------------------------+---------------

Thanks x 1000

 

[r937]

use two selects and union them together

one for id in (5,6), the other for a self-join of service_type_id equal to table_id

r937.com | rudy.ca

 

[Sleidia]

Thanks!
I thought CASE could simplify everything but apparently it's not the ... case