How to construct a sql to select from a column only when the value of the column is not a certain value (e.g. null, 0, etc), otherwise select from another column?
Lets assume that you have two tables, Fruit and Apple, in your database.
table Fruit
name price
Apple 1.69
Orange 0.98
Pear 1.29
table Apple
name price
Golden Delicious 1.49
Fuji 1.89
Gala 0
The following sql gets the price for each type of apple. If the price in the Apple table for that type of apple is 0, then use the Apple price in the Fruit table.
SELECT a.name name
CASE a.price //the column
WHEN 0 THEN f.price //if a.price=0, gets f.price
ELSE a.price //else gets a.price
END AS price //the resulting field name is price
FROM Apple a, Fruit f
WHERE f.name='Apple';
The general form of the sql for getting value from another column if value is a certain known value is as below.
SELECT
CASE <columnA>
WHEN <value1> THEN <columnB> //e.g. WHEN NULL or WHEN LIKE '%ard%'
WHEN <value2> THEN <columnC>
......
ELSE <columnA>
END AS <field name in the result set>
FROM <list of tables>
-----------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
Lets assume that you have two tables, Fruit and Apple, in your database.
table Fruit
name price
Apple 1.69
Orange 0.98
Pear 1.29
table Apple
name price
Golden Delicious 1.49
Fuji 1.89
Gala 0
The following sql gets the price for each type of apple. If the price in the Apple table for that type of apple is 0, then use the Apple price in the Fruit table.
SELECT a.name name
CASE a.price //the column
WHEN 0 THEN f.price //if a.price=0, gets f.price
ELSE a.price //else gets a.price
END AS price //the resulting field name is price
FROM Apple a, Fruit f
WHERE f.name='Apple';
The general form of the sql for getting value from another column if value is a certain known value is as below.
SELECT
CASE <columnA>
WHEN <value1> THEN <columnB> //e.g. WHEN NULL or WHEN LIKE '%ard%'
WHEN <value2> THEN <columnC>
......
ELSE <columnA>
END AS <field name in the result set>
FROM <list of tables>
-----------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
No comments:
Post a Comment