Product qty in a given branch

Hi,
I'm new to queries and I'm stuck with joins...
I have the following tables, and would like to get the total of the
products, given a BranchId:
table Product
ProductId: PK
ProductName
table Branch
BranchId
table Inventory
InventoryId: PK
BranchId: FK to Branch.BranchId
table InventoryDetail
InventoryDetailId: PK
InventoryId: FK to Inventory.InventoryId
ProductId: FK to Product.ProductId
Quantity
Here is what I started:
CREATE PROCEDURE SelectProductByName(@.BranchId INT, @.ProductName
NVARCHAR(100))
AS
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail AS ID ON ID.ProductId = P.ProductId
/*And waht now with*/
RETURNHello, Michael
I guess you want something like this:
SELECT P.ProductId, P.ProductName, X.Qty
FROM Product AS P LEFT JOIN (
SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
FROM InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
WHERE I.BranchId = @.BranchId
GROUP BY ID.ProductId
) X ON P.Product = X.ProductId
WHERE P.ProductName = @.ProductName
This query may also work, but the first query is better:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Razvan|||Hi Razvan,
Thank you very much for the help.
Why is the first first query better than the second one?
Also, I've come up with the following query. Is it correct?
SELECT P.ProductId, P.ProductName,
(SELECT SUM(ID.Quantity) FROM InventoryDetail AS ID
JOIN Inventory AS I ON I.InventoryId = ID.InventoryId
WHERE (I.BranchId = @.BranchId) AND (ID.ProductId = P.ProductId))
AS Qty
FROM Product AS P
WHERE (P.ProductName = @.ProductName)
Regards
Razvan Socol wrote:
> Hello, Michael
> I guess you want something like this:
> SELECT P.ProductId, P.ProductName, X.Qty
> FROM Product AS P LEFT JOIN (
> SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
> FROM InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> WHERE I.BranchId = @.BranchId
> GROUP BY ID.ProductId
> ) X ON P.Product = X.ProductId
> WHERE P.ProductName = @.ProductName
> This query may also work, but the first query is better:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
> LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
> AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Razvan
>|||Hello, Michael

> Why is the first first query better than the second one?
Before testing the queries (because I had no DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
query is better, because the second query seemed unsafe to me. Now that
I've tested it (by translating it for one of my own schemas), I can say
that the second query is plain wrong: it doesn't return the expected
results. I intended to write the second query this way:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Regarding the weird placement of the ON clause, see:
http://groups.google.com/group/micr...br />
2579509b

> Also, I've come up with the following query. Is it correct?
Yes, it is correct. Actually, I think it's identical (in terms of
performance) with my first query, but your query is better because is
more readable. The corrected second query (although it produces an
execution plan with the same cost as the other two), seems to me a bit
slower, because the "Stream Aggregate" step (the GROUP BY clause) is
done on a wider row. Also, it's a lot harder to understand, so that's
why I still think it's the worse among the three queries.
Razvan|||Hi Razvan,
Sorry, I didn't provide the DDL. Now that I know how to do it, I'll
provide it next time.
I'll go on with my query as I already understood it, keeping a copy of
yours if needed.
Thanks a lot
Razvan Socol wrote:
> Hello, Michael
>
>
> Before testing the queries (because I had no DDL and sample data, see:
> http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
> query is better, because the second query seemed unsafe to me. Now that
> I've tested it (by translating it for one of my own schemas), I can say
> that the second query is plain wrong: it doesn't return the expected
> results. I intended to write the second query this way:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Regarding the weird placement of the ON clause, see:
> http://groups.google.com/group/micr... />
492579509b
>
>
> Yes, it is correct. Actually, I think it's identical (in terms of
> performance) with my first query, but your query is better because is
> more readable. The corrected second query (although it produces an
> execution plan with the same cost as the other two), seems to me a bit
> slower, because the "Stream Aggregate" step (the GROUP BY clause) is
> done on a wider row. Also, it's a lot harder to understand, so that's
> why I still think it's the worse among the three queries.
> Razvan
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Since you also did not give any specs, can I assume that inventory is
spread over branches? Can I assume the product is identified by some
industry standard code, such as a UPC? And why did you create that
useless inventory details table? An inventory is not like an invoice.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
product_name CHAR(20) NOT NULL,
.);
CREATE TABLE Branches
(branch_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Inventory
(branch_id INTEGER NOT NULL
REFERENCES Branches(branch_id),
upc CHAR(13) NOT NULL
REFERENCES Products(upc),
PRIMARY KEY (branch_id, upc),
qty_on_hand INTEGER DEFAULT 0 NOT NULL
CHECK (qty_on_hand >= 0)
The right design makes it easy:
SELECT P.upc, SUM(I.qty_on_hand)
FROM Products AS P
LEFT OUTER JOIN
Inventory AS I
ON P.upc= I.upc
GROUP BY P.upc;|||Hi Celko,
I already got an answer from Razvan.
I needed the inventory detail table to hold the different quantities
with their respective notes, such as bin location, qty of damaged items
included in the inventory,...
Now that I know how to post DDL, I'll do it next time. As for the sample
data, I don't have any yet. I'm starting to design the database from
scratch.
Thanks for the help
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Since you also did not give any specs, can I assume that inventory is
> spread over branches? Can I assume the product is identified by some
> industry standard code, such as a UPC? And why did you create that
> useless inventory details table? An inventory is not like an invoice.
> CREATE TABLE Products
> (upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
> product_name CHAR(20) NOT NULL,
> ..);
> CREATE TABLE Branches
> (branch_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE Inventory
> (branch_id INTEGER NOT NULL
> REFERENCES Branches(branch_id),
> upc CHAR(13) NOT NULL
> REFERENCES Products(upc),
> PRIMARY KEY (branch_id, upc),
> qty_on_hand INTEGER DEFAULT 0 NOT NULL
> CHECK (qty_on_hand >= 0)
>
>
> The right design makes it easy:
> SELECT P.upc, SUM(I.qty_on_hand)
> FROM Products AS P
> LEFT OUTER JOIN
> Inventory AS I
> ON P.upc= I.upc
> GROUP BY P.upc;
>|||>> I needed the inventory detail table to hold the different quantities with
their respective notes, such as bin location, qty of damaged items included
in the inventory,...<<
That is the usual inventory design; several different quantity
attributes (on-hand, on order, on hold, damaged, expired, etc. Since
these are all distinct attributes of an inventory held at a p[articular
branch, you simply extend the table. That data and the needed check
constraints were not shown in your specs (or lack of specs, really).
There is no need to create a separate inventory_id., which is a poor
attempt at a surrogate for a branch_id. You are splitting the actual
primary key (branch_id, upc) across tables. Ask yourself what meaning
an inventory has apart from the goods it holds. Look at your
pseudo-code; what maps yhour magical "InventoryDetailId" to one and
only one product, and assures that it is the RIGHT product? Nothing.
The split key invites disaster.
Please consider a correct design instead.|||Designing a database is not so simple...
I've already changed my design several times. At first because of a lack
of knowledge in database design (this is actually my first database with
SqlServer, before this was 2 simple databases with MS Access)
If you don't mind I'll really need a help. I'll try to make it as simple
as I can.
Our company has 2 branches (I'll generalise this to several branches).
Products can be stocked in either of these branches.
When counting a product, we need the date of the counting, and the
details (quantity + comments, such as damaged, carton no, bin
location,... which are "free-hand" text note). Note that branches count
their products independently of each other.
So, I came up with these tables:
CREATE TABLE Product (
ProductId BIGINT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Branch (
BranchId INT IDENTITY PRIMARY KEY,
BranchName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Inventory (
InventoryId BIGINT IDENTITY PRIMARY KEY,
InventoryDate DATETIME NOT NULL,
BranchId INT NOT NULL REFERENCES Branch(BranchId),
..
)
CREATE TABLE InventoryDetail (
InventoryDetailId BIGINT IDENTITY PRIMARY KEY,
InventoryId BIG INT NOT NULL REFERENCES Inventory(InventoryId),
ProductId BIG INT NOT NULL REFERENCES Product(ProductId),
Quantity INT NOT NULL,
Comments NVARCHAR(100) NULL
)
As for sample data, I don't have any yet, but here is what we want:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has specially ordered these for himself
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already
--CELKO-- wrote:
>
> That is the usual inventory design; several different quantity
> attributes (on-hand, on order, on hold, damaged, expired, etc. Since
> these are all distinct attributes of an inventory held at a p[articular
> branch, you simply extend the table. That data and the needed check
> constraints were not shown in your specs (or lack of specs, really).
> There is no need to create a separate inventory_id., which is a poor
> attempt at a surrogate for a branch_id. You are splitting the actual
> primary key (branch_id, upc) across tables. Ask yourself what meaning
> an inventory has apart from the goods it holds. Look at your
> pseudo-code; what maps yhour magical "InventoryDetailId" to one and
> only one product, and assures that it is the RIGHT product? Nothing.
> The split key invites disaster.
> Please consider a correct design instead.
>