How can I join two tables to get an output in a view-CodePudding

I'm trying to calculate the overall value of a transaction by doing amount*price_for_each.

As an example, 5 Garlic, 8 Bread,1 Chicken and 1 Rice. The price of (from Items) of Garlic is 25, Bread is 200,Chicken is 450and Rice is 200. 5⋅25 8⋅200 1⋅450 1⋅200=2125

CREATE TABLE Items(
price_for_each INT,
name VARCHAR(20),
PRIMARY KEY(name)
);

CREATE TABLE ItemsInTransactions(
name VARCHAR(20),
t_id INT,
amount INT,
FOREIGN KEY(name) references Items(name),
FOREIGN KEY(t_id) references Transactions(t_id)
);

CREATE VIEW TransactionValue AS
SELECT DISTINCT t_id,sum(amount*price_for_each) as value
FROM Items price_for_each, ItemsInTransactions amount 
GROUP BY t_id
ORDER BY t_id;

I believe the problem is in my view, the outputted values are much higher than anticipated which leads me to believe there is an arithmetic error (maybe multiplying too much). If anyone can explain a better way to calculate my output this would be greatly appreciated

enter image description here

CodePudding user response：

FROM Items price_for_each, ItemsInTransactions amount is the old deprecated way to do a cross join (producing every combination of Items and ItemsInTransactions records). You want an inner join instead:

FROM ItemsInTransactions amount
INNER JOIN Items price_for_each on price_for_each.name=amount.name

so you are only looking up the price for each for the item in question.

Some other comments: DISTINCT is redundant here, your group by already assures you will only have one row for each t_id. table aliases should help document what your query is doing or just allow you to refer to table fields more tersely; here you are using aliases that describe what you are getting from the table, which isn't particularly useful.

CodePudding user response：

you a re using a cross join which will combine all available crows

Better is always to use joins

CREATE VIEW TransactionValue AS
SELECT t_id,sum(amount*price_for_each) as value
FROM Items i JOIN  ItemsInTransactions iit ON i.name  = iit.name 
GROUP BY t_id
ORDER BY t_id;