case when then php,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

回答1:

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

回答2:

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.

回答3:

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

回答4:

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.