COALESCE or LIMIT?

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?

== Quote from Captain Paralytic (paul_lautman@yahoo.com)'s article
> 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?
if class is part of the pk, can it be null?

--
POST BY: lark with PHP News Reader

On 27 Jul, 14:20, lark <ham...@sbcglobal.net> wrote:
| if class is part of the pk, can it be null?

What a strange question.

First from the manual:
"A PRIMARY KEY is a unique index where all key columns must be defined
as NOT NULL."
so any field that is part of a primary key cannot be NULL.

Now whilst I can accept that you didn't know that, the post started
with the CREATE TABLE statement:

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

The words "NOT NULL" following "`class` varchar(1)" explictly state
that class cannot be NULL (otherwise the statement would have failed
due to the primary key constraint).

== Quote from Captain Paralytic (paul_lautman@yahoo.com)'s article
> On 27 Jul, 14:20, lark <ham...@sbcglobal.net> wrote:
> | if class is part of the pk, can it be null?
> What a strange question.
> First from the manual:
> "A PRIMARY KEY is a unique index where all key columns must be defined
> as NOT NULL."
> so any field that is part of a primary key cannot be NULL.
> Now whilst I can accept that you didn't know that, the post started
> with the CREATE TABLE statement:
> 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
> The words "NOT NULL" following "`class` varchar(1)" explictly state
> that class cannot be NULL (otherwise the statement would have failed
> due to the primary key constraint).

looks like it's null in the data you posted.
--
POST BY: lark with PHP News Reader

On 27 Jul, 15:40, lark <ham...@sbcglobal.net> wrote:
> == Quote from Captain Paralytic (paul_laut...@yahoo.com)'s article
>
>
>
>
>
> > On 27 Jul, 14:20, lark <ham...@sbcglobal.net> wrote:
> > | if class is part of the pk, can it be null?
> > What a strange question.
> > First from the manual:
> > "A PRIMARY KEY is a unique index where all key columns must be defined
> > as NOT NULL."
> > so any field that is part of a primary key cannot be NULL.
> > Now whilst I can accept that you didn't know that, the post started
> > with the CREATE TABLE statement:
> > 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
> > The words "NOT NULL" following "`class` varchar(1)" explictly state
> > that class cannot be NULL (otherwise the statement would have failed
> > due to the primary key constraint).
>
> looks like it's null in the data you posted.
> --
> POST BY: lark with PHP News Reader- Hide quoted text -
>
> - Show quoted text -

Not to me it doesn't. It looks like it is the empty string.

If it was NULL it would say:

INSERT INTO `price_table` (`class`, `item`, `price`) VALUES
(NULL, 'A', 50),
('1', 'A', 60),
(NULL, 'B', 30),
(NULL, 'C', 20),
('2', 'C', 0);