CODEWITHSUNDEEP
mysqlselectcasecase-when
PCG
PCG

Reputation: 2299

How could I select a column based on another column in mySQL?

The following script works fine. But I want to write it in one line rather than three lines. 'Size" is passed from my main program and its used here to test. Simply I want to get Price based on size.

Table columns : LISTING_ID, PRICE_LARGE_PRICE, PRICE_SMALL_PRICE.

SET @Size = 'SMALL';

SELECT 
    PRICE_LARGE_PRICE,PRICE_SMALL_PRICE
INTO
    @PRICE_LARGE_PRICE,@PRICE_SMALL_PRICE
FROM
    prices
WHERE
    PRICE_LISTING_ID = 60;

SET @ITEM_PRICE = (CASE @Size WHEN 'REGULAR' THEN @PRICE_LARGE_PRICE 
WHEN 'SMALL' THEN @PRICE_SMALL_PRICE 
ELSE null 
END); 

SELECT @ITEM_PRICE;

Any help is appreciated.

Upvotes: 1

Views: 304

Answers (2)

Evert
Evert

Reputation: 99541

I think you want

SELECT 
  IF(@size == 'SMALL', PRICE_SMALL_PRICE, PRICE_LARGE_PRICE) AS ITEM_PRICE 
FROM prices;

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Following may work.

SET @Size = 'SMALL';

SELECT 
    PRICE_LARGE_PRICE,
    PRICE_SMALL_PRICE, 
    CASE WHEN @Size = 'REGULAR' THEN PRICE_LARGE_PRICE
         WHEN @Size = 'SMALL' THEN PRICE_SMALL_PRICE
    END AS ITEM_PRICE 
INTO
    @PRICE_LARGE_PRICE,
    @PRICE_SMALL_PRICE, 
    @ITEM_PRICE 
FROM
    prices
WHERE
    PRICE_LISTING_ID = 60;

Upvotes: 1

Related Questions