CODEWITHSUNDEEP
phpmysql
Ket.
Ket.

Reputation: 774

except condition in mysql query..

i have table with following structure, 

name (type: TEXT)
price (type: INT)
color (type: TEXT)
vehicletype (type: TEXT) eg. SEDAN/SUV

now i want to retrieve (name, price, color, vehicletype) If its a black sedan, I'm ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won't go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.
i have this query, but it's not worked, 

SELECT name, price, color, vehicletype FROM carrecords WHERE
(vehicletype = 'SEDAN' AND color  = 'black' AND price <= 10000) OR
(vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 ) OR
(vehicletype  = 'SEDAN' /* here I tried != "SUV" as well - no luck*/  AND color NOT IN('red','white','black') AND price <= 7000) OR
(vehicletype = 'SUV' AND color  = 'black' AND price <= 15000) OR
(vehicletype = 'SUV' AND color  != 'black' AND price <= 14000)
ORDER BY price ASC

so, is there any solution?

Upvotes: 0

Views: 661

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65264

SELECT name, price, color, vehicletype FROM carrecords WHERE
-- black sedans up to 10k
(vehicletype = 'SEDAN' AND color  = 'black' AND price <= 10000)
-- red or white sedans up to 8k
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
-- black SUV up to 15k
OR (vehicletype = 'SUV' AND color  = 'black' AND price <= 15000)
-- non-black SUV up to 14k
OR (vehicletype = 'SUV' AND color  != 'black' AND price <= 14000)
-- any other vehicle up to 7k
OR (price <= 7000)
ORDER BY price ASC

Upvotes: 4

Related Questions