AndyMarty AndyMarty - 4 months ago 8
SQL Question

MySQL Query: Display Specific Column

Let's say I have five columns in my table.

Column 1: Name of Store and columns 2 to 5: Store Types

For Name of Store, it will basically just contain the name of the stores. For columns 2-5, it will contain either YES or NO depending on what store type it is. For example:

StoreName | Clot. | Food | App. | Shoes
Mcdonalds | NO | YES | NO | NO
MC ShoeStore | NO | NO | NO | YES


Now I want to come up with a query wherein I'd display all stores starting with the letter M and their store type without the other store type with 'NO' in it appearing.

I only know:

SELECT NameOfStore from table where NameOfStore LIKE '%m'


I'm not sure on how I'm going to display the store type where it contains a YES. or is that possible?

Answer

You could simply use a case statement. Also, your WHERE clause criteria was wrong.

SELECT
    NameOfStore,
    CASE
        WHEN Clothes = 'YES' THEN 'Clothes'
        WHEN Fastfood = 'YES' THEN 'Fastfood'
        WHEN Appliances = 'YES' THEN 'Appliances'
        WHEN Shoes = 'YES' THEN 'Shoes'
    END AS TypeOfStore
FROM
    table
WHERE
    LOWER(NameOfStore) LIKE 'm%';

Handling multiple store types for a single store:

SELECT
    NameOfStore,
    CONCAT(' & ', --This is the separator to use - you can replace this with whatever
    CASE WHEN Clothes = 'YES' THEN 'Clothes' END,
    CASE WHEN Fastfood = 'YES' THEN 'Fastfood' END,
    CASE WHEN Appliances = 'YES' THEN 'Appliances' END,
    CASE WHEN Shoes = 'YES' THEN 'Shoes' END
    ) AS TypeOfStore
FROM
    table
WHERE
    LOWER(NameOfStore) LIKE 'm%';

Your output would look something like:

NameOfStore   |   TypeOfStore
-----------------------------
McDonalds     |   Fastfood
Mike's Shop   |   Clothes & Shoes