由于SQL语法错误，SQL查询无法通过？ phpMyAdmin的

Question

I am typing in this query:

i13 display name, price and sum quantity for all fruit, even ones with quantity null

select inventory.fruitID
concat( "$", quantity * price ) as "value"
from fruit right join inventory
on fruit.fruitID = inventory.fruitID;

I am getting this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( "$", quantity * price ) as "value" from fruit right join inventory on fruit.fr' at line 3

What am I doing wrong?

Answer 1

First, if you want all fruit, then use a left join with fruit as the first table. I think the query you are trying for is:

select f.fruitID, f.price, sum(i.quantity) as total_quantity
from fruit f left join
     inventory i
     on f.fruitID = i.fruitID
group by f.fruitID, f.price;

Notes:

• Table aliases make the query easier to write and to read.
• This assumes that price is in the fruit table.
• If you want all fruit, then fruit should be the first table in the left join.
• You see to want to sum the inventory, so that requires an aggregation function and group by.