[mysql] SELECT CASE WHEN THEN (SELECT)

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

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.

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?).

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.

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.