This is a discussion on COALESCE or LIMIT? within the MySQL Database forums, part of the Database Forums category; I need to solve a problem similar to the language one whereby if a translation in the chosen languange does ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
I need to solve a problem similar to the language one whereby if a
translation in the chosen languange does not exist, the default one should be selected. My problem is actually picking a price specific to a certain class if available or a default price otherwise. So taking a table like: CREATE TABLE `price_table` ( `class` varchar(1) NOT NULL, `item` varchar(1) NOT NULL, `price` int(11) NOT NULL, PRIMARY KEY (`item`,`class`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `price_table` (`class`, `item`, `price`) VALUES ('', 'A', 50), ('1', 'A', 60), ('', 'B', 30), ('', 'C', 20), ('2', 'C', 0); to find the price of item A I could do: SELECT `price` FROM `price_table` WHERE `item` = '$item' AND (`class` = '$class' OR `class` = '') ORDER BY `class` DESC LIMIT 1 so if $item was 'A' $class was '1', I would get 60, otherwise I will get 50. Or I could do SELECT COALESCE(`s`.`price`,`d`.`price`) `price` FROM `price_table` `d` LEFT JOIN `price_table` `s` ON `s`.`item` = `d`.`item` AND `s`.`class` = '$class' WHERE `d`.`item` = '$item' AND `d`.`class` = '' My inclination is to go with the LEFT JOIN, but what do others think? |