SELECT CASE WHEN THEN SELECT

Question

I am trying to select a different set of results for a product depending on a product type  So if my product should be a book I want it to look up the UPC and Artist for a normal product these details are however irrelevant and for another product I would want a completely different set of results   SELECT CASE Product type id     WHEN 10 THEN           SELECT          Product product id           Product type id           Product product name           Product UPC          Product Type type          CONCAT WS       first name  middle name  last name   AS artistC          FROM Product  Product Type  Product ArtistAuthor          WHERE Product type id   Product Type type id          AND Product product id    pid         AND Product artist id   Product ArtistAuthor artist id           ELSE           SELECT          Product product id           Product type id           Product product name          Product Type type          FROM Product  Product Type          WHERE Product type id   Product Type type id          AND Product product id    pid       END FROM Product  WHERE Product product id    pid   I am not sure where I am going wrong

User · Answer

You should avoid using nested selects and I would go as far to say you should never use them in the actual select part of your statement. You will be running that select for each row that is returned. This is a really expensive operation. Rather use joins. It is much more readable and the performance is much better.

In your case the query below should help. Note the cases statement is still there, but now it is a simple compare operation.

select
    p.product_id,
    p.type_id,
    p.product_name,
    p.type,
    case p.type_id when 10 then (CONCAT_WS(' ' , first_name, middle_name, last_name )) else (null) end artistC
from
    Product p

    inner join Product_Type pt on
        pt.type_id = p.type_id

    left join Product_ArtistAuthor paa on
        paa.artist_id = p.artist_id
where
    p.product_id = $pid

I used a left join since I don't know the business logic.

User · Answer

I ended up leaving the common properties from the SELECT queries and making a second SELECT query later on in the page  I used a php IF command to call for different scripts depending on the first SELECT query  the scripts contained the second SELECT query

User · Answer

For a start the first select has 6 columns and the second has 4 columns  Perhaps make both have the same number of columns  adding nulls

User · Answer

You Could try the other format for the case statement  CASE WHEN Product type id   10 THEN     Select Statement   ELSE     Other select statement      END FROM Product  WHERE Product product id    pid   See http   msdn microsoft com en-us library ms181765 aspx for more information

[mysql] SELECT CASE WHEN THEN (SELECT)

Examples related to mysql

Examples related to select

Examples related to case