Wrong Wrong - 7 months ago 32
SQL Question

Multiplicate two fields from different columns - SQL

I have the following two tables:

............................................

Product | Game | Quantity

1------------2-----------3

1------------3-----------3

............................................

GameID | Price

2--------------5

3--------------8

...........................................

How can I multiply quantity * price for each game using a WHERE or something similar (as I want to get the total cost for Product 1, for example) in MS Access? Game is a foreign key to GameID which is a primary key.
I know it's with JOINS but I can't make it work.

Thanks and regards

Answer

You need to perform a JOIN between the tables like below. See Documentation For more information

select p.Product,
p.Game,
p.Quantity * q.Price as calculated_column
from Producttab p
inner join gametab q on p.Game = q.GameID
where p.Product = 3;

Looks like it's complaining about those table alises. Here is MS-Access Version:

select Producttab.Product,
Producttab.Game,
Producttab.Quantity * gametab.Price as calculated_column
from Producttab
inner join gametab on Producttab.Game = gametab.GameID
where Producttab.Product = 3;