0

Here is the sample :

SELECT toys.name, model.name
FROM model, toys

LEFT JOIN model
ON toys.model = model.name

WHERE model.name IN (robots,cops,trucks) toys.price = 100 or toys.price = 10

If I found toys in the list I want the toys.price = 100 else toys.price = 10

Powerup California
  • 5
  • 3
  • What are you trying to do exactly? I'm not sure I understand your question – Antony Jan 25 '17 at 15:18
  • You have an error in your query, in your where clause. Can you please give us the correct query? – Blaatpraat Jan 25 '17 at 15:21
  • if there are toys with price over 100, retrieve toys with price of 100. Else, retrieve toys with price 10. Is this what you are trying to do? – David Neto Jan 25 '17 at 15:22
  • there are two tables model and toys , i want to sort the toys based on the toys price. If I find the any toy model IN (trucks, cops, ) the price will be 100. if not the toy price = 10. there are only two prices – Powerup California Jan 25 '17 at 15:22

2 Answers2

0

Try it with this query:

SELECT `toys`.`name`, `model`.`name`, IF (`model`.`name` IN ('robots', 'cops', 'truck'), 100, 10) AS price
FROM `toys`

LEFT JOIN `model`
ON `toys`.`model` = `model`.`name`

First of all: I used quotes and backticks in the query.
You can read the reason on this StackOverflow post
Always use them!

Second of all: no need to have 2 tables in the from, if you're joining them.

Third of all: the solution you want, can be solved with the if function:
If the model name is one if these three, the price will be 100, otherwise it will be 10.

Blaatpraat
  • 2,829
  • 11
  • 23
0

you can achieve it by using bracket and OR. And you have to use quotes in your model name as they are string. Here is a sample query

SELECT toys.name, model.name
FROM toys
LEFT JOIN model
ON toys.model = model.name
WHERE (model.name IN ('robots','cops','trucks') AND toys.price = 100) OR toys.price = 10
reza
  • 1,507
  • 2
  • 12
  • 17