CODEWITHSUNDEEP
mysql
myworkaccount
myworkaccount

Reputation: 139

MySQL, using columns in another table for a CASE statement

I have two tables, an Items table that looks like this:

Items

Item       Count
-----------------
1          20
2          24
3          49

And a table called ItemsConfig that looks like this:

ItemsConfig

ItemCountLow    ItemCountHigh    ItemCountStatus
------------------------------------------------
0               20               Low
21              50               Normal
51              100              Surplus

What I would like to do is build a query that will compare item counts in the Items table using the ItemCountLow and ItemCountHigh thresholds in the ItemsConfig table, to derive the ItemCountStatus.

Here are the create statements for each of these tables

Items table

CREATE TABLE IF NOT EXISTS `Items` (
  `Item` int(11) NOT NULL,
  `Count` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Items`
--

INSERT INTO `Items` (`Item`, `Count`) VALUES
(1, 20),
(2, 24),
(3, 49);

ItemsConfig table

CREATE TABLE IF NOT EXISTS `ItemsConfig` (
  `ItemCountLow` int(11) NOT NULL,
  `ItemCountHigh` int(11) NOT NULL,
  `ItemCountStatus` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ItemsConfig`
--

INSERT INTO `ItemsConfig` (`ItemCountLow`, `ItemCountHigh`, `ItemCountStatus`) VALUES
(0, 20, 'Low'),
(21, 50, 'Normal'),
(51, 100, 'Surplus');

I'm trying to do something like this, but can't seem to figure it out.

SELECT item, count, 
CASE
    WHEN count > ItemsConfig.ItemCountLow AND count < ItemsConfig.ItemCountHigh  
        THEN ItemsConfig.ItemCountStatus
END as 'status'
FROM Items

Could someone please help?

Upvotes: 1

Views: 1226

Answers (1)

Jlil
Jlil

Reputation: 170

How about:

SELECT i.item, i.count, ifnull(ic.ItemCountStatus,'Unknown') status
FROM items i
LEFT JOIN ItemsConfig ic on (i.count between ic.ItemCountLow  AND ic.ItemCountHigh)

Upvotes: 2

Related Questions