CODEWITHSUNDEEP
sqlms-access
Wrong
Wrong

Reputation: 1263

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

Upvotes: 0

Views: 66

Answers (1)

Rahul
Rahul

Reputation: 77896

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;

Upvotes: 1

Related Questions