Join Problem

Hi

I have three tables in my db called Products, Receives, Issues.

Products

ProductID, ProductName

Receives

ProductID, ReceiveQty

Issues

ProductID, IssueQty

I want to pass an ProductID and display columns from a sproc like this -

ProductName, ReceiveQty, IssueQty, BalanceQty ( ReceiveQty - IssueQty )

A record may exist or not in the Issues table for a given ProductID. What will be the syntex to write this sproc?

Regards

Kapalic

Something like this:

Code Snippet

SELECT
p.ProductName,
isnull( r.ReceiveQty, 0 ),
isnull( i.IssueQty, 0 ),
BalanceQty = ( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID

I must note: this seems like a very odd design, and may be troublesome to properly 'tune'.

|||

Try the following, it may be helpful for you.

Code Snippet

select ProductName, ReceiveQty, IssueQty, (ReceiveQty-IssueQty) as BalanceQty
from
(
select Products.ProductName,
isnull((select sum(ReceiveQty) from Receives where Receives.ProductID=Products.ProductID),0) as ReceiveQty,
isnull((select sum(IssueQty) from Issues where Issues.ProductID=Products.ProductID),0) as IssueQty
from Products where Products.ProductID = yourProductId
) as temp

|||to:Arnie Rowland

what will happen if i recieve a product 2 times or more?

|||

You make a good observation. I should have included a [GROUP BY].

I think that this revision will be more in line with an acceptable solution:

Code Snippet

SELECT
p.ProductName,
p.ProductID,
dt.ReceiveQty,
dt.IssueQty,
dt.BalanceQty
FROM Products p
JOIN (SELECT
p.ProductID,
ReceiveQty = sum( isnull( r.ReceiveQty, 0 )),
IssueQty = sum( isnull( i.IssueQty, 0 )),
BalanceQty = sum( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
LEFT JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
WHERE p.ProductID = @.ProductID
GROUP BY ProductID
) dt
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

|||but, what will happen if i recieve a kind of product but never issue?|||

IssueQty will be 0 (zero) if no rows exist in the Issues table.

ReceiveQty will be 0 (zero) if no rows exist in the Receives table.

|||

Arnie

I m getting incorrect result - while I enter multiple receive record, every of three qty become double of actual result! Whats the prob?

Kapalic

|||

In order by continue assisting you, it would be useful to have sample data to test.

Please include table DDL and sample data in the form of INSERT statements.

|||

Kapalic,

First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.

Hopefully, this code will get you what you want.

-Arnie

Code Snippet

DECLARE @.Products table

( ProductID int,

ProductName varchar(25)

)

DECLARE @.Receives table

( ProductID int,

ReceiveQty int

)

DECLARE @.Issues table

( ProductID int,

IssueQty int

)

INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )

INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )

INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )

DECLARE @.ProductID int

SET @.ProductID = 1

-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,

ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))

FROM @.Receives

WHERE ProductID = @.ProductID

),

IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))

FROM @.Issues

WHERE ProductID = @.ProductID

)

) dt

JOIN @.Products p

ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

|||

Dear Arnie,

Thank you very much for your great help! This sproc is now working allright. Will you please tell me what was wrong with the sproc?

And I need a bit more help. I can't understand how to modify the sproc so that it displays all the products which are listed in the product table. Please help!!

Regards

Kapalic

|||

Code Snippet

SELECT
p.ProductName,
p.ProductID,
p.AdditionalProductsColumnsHere,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),

You can add additional columns from the Products table in the outer query. Prefix each column with the alias 'p'.
|||

I didn't want to show another column. I wanted to show all the records from the products table, not just a single record about a product. I couldn't make myself clear.

Thnx

Kapalic

|||

Arnie Rowland wrote:

Kapalic,

First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.

Hopefully, this code will get you what you want.

-Arnie

Code Snippet

DECLARE @.Products table

( ProductID int,

ProductName varchar(25)

)

DECLARE @.Receives table

( ProductID int,

ReceiveQty int

)

DECLARE @.Issues table

( ProductID int,

IssueQty int

)

INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )

INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )

INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )

DECLARE @.ProductID int

SET @.ProductID = 1

-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,

ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))

FROM @.Receives

WHERE ProductID = @.ProductID

),

IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))

FROM @.Issues

WHERE ProductID = @.ProductID

)

) dt

JOIN @.Products p

ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

Can you tell me how to post a sample code with color?

|||

It's very easy. Copy the code to MS Word, and copy and paste it from Word to post. It will appear colorful.

Kapalic