Table Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Join Problem

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-02-19 : 13:06:21

I created a view that works great. It displays one record for every record in our Item Master (IV00101) using summations of the sales detail table (SOP10200).

Here's the current script:

ALTER VIEW tec_item_supply_demand
as
Select IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND,
sum(case when SOP10200.SOPTYPE = '2' then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' then QUANTITY else 0 end) as QUOTE_QTY
from IV00101
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
where IV00102.LOCNCODE = 'WAREHOUSE'
Group by IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND

my problem is I now have to add another table - SOP10100 table is a header table - it does not have any ITEMNMBR field that I could join. Therefore, I have to join the SOP10100 to the SOP10200 table using SOPNUMBE field.

However when I do this - I get only one record for every item in the SOP10200 table (not one record for every record in IV00101 table as desired) here's how I added the SOP10100 table join (and restriction - this table has a voidstts column that I need to consider)

ALTER VIEW tec_item_supply_demand
as
Select IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND,
sum(case when SOP10200.SOPTYPE = '2' then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' then QUANTITY else 0 end) as QUOTE_QTY
from IV00101
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
where IV00102.LOCNCODE = 'WAREHOUSE' AND SOP10100.VOIDSTTS <> 1
Group by IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND

by adding this table in this manner - my results only display records in the SOP10200 table - which is not my desired result.

So, I am looking for suggestions on how to add additional table, restrict my calculations based on a condition in this table and return all item numbers in the IV00101 table....I think my problem is in the 'where' statement - is there a way to add the restriction without using 'AND'?

Thank you in advance for your assistance.....

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 13:22:09

The way you have the joins looks OK, but the WHERE is restricting the results to only those rows where SOP10100.VOIDSTTS <> 1. So now if you have a row in IV00101, and all the joined rows in SOP10100 have VOIDSTTS = 1 or if there are no joined rows in SOP10100 then you will lose that row in IV00101.

I don't know what you're trying to do with VOIDSTTS but it may be just the cases where there are no rows in SOP10100. To fix that just add a null check to your WHERE, like this

ALTER VIEW tec_item_supply_demand
as 
Select IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND,
sum(case when SOP10200.SOPTYPE = '2' then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' then QUANTITY else 0 end) as QUOTE_QTY
from IV00101 
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
where IV00102.LOCNCODE = 'WAREHOUSE' AND (SOP10100.VOIDSTTS <> 1 OR SOP10100.VOIDSTTS IS NULL)
Group by IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00102.QTYONHND

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-19 : 13:26:05

[code]ALTER VIEW tec_item_supply_demand
as
Select IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND,
sum(case when SOP10200.SOPTYPE = '2' then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' then QUANTITY else 0 end) as QUOTE_QTY
from IV00101
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR and IV00102.LOCNCODE = 'WAREHOUSE'
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE AND SOP10100.VOIDSTTS <> 1
Group by IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND][/code]

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 13:35:17

I've always felt that it is better to keep non-join expressions in the WHERE rather than putting them in the JOIN clauses unless it would change the results (as happens in the case where you check for nulls). Peso, do you have a specific reason for saying that they should be in the JOIN clauses in this case?

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-02-19 : 13:54:33

Thanks very much for quick responses - I probably should have been a bit clearer what the restriction of SOP10100.VOIDSTTS does. Basically, if the user 'voids' a sales transaction (each sales transaction is stored in two tables - sop10100 (header - documnet information and customer information) and sop10200 (item detail - item ordered and qty, price, etc) - then I do not want to sum the sop10200 table for quantity) therefore, I added the restriction where it probably best belongs as follows:

ALTER VIEW tec_item_supply_demand
as
Select IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND,
sum(case when SOP10200.SOPTYPE = '2' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as QUOTE_QTY
from IV00101
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR and IV00102.LOCNCODE = 'WAREHOUSE'
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
Group by IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND

This seems to work fine (I need to test by voiding some transactions and verifying they are not in results returned).

However, I noticed an unexpected result - my results returned included some 'null' values in the QTYONHND column. This is because not every record in IV00101 has a record in IV00102 where LOCNCODE = 'WAREHOUSE'. Is there a way to be certain that the returned value for QTYONHND is '0' if there is no item record with LOCNCODE = 'WAREHOUSE' in IV00102?

Thanks again - everyone in this forum is great. I really try to self-teach and I never just post without attempting (many times) to solve on my own.......but sometimes I really get stuck...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-19 : 14:05:44

snSQL, Jeff has an excellent article about why to put the conditions of a JOIN rather in the JOIN than a WHERE, on a LEFT/RIGHT join operation. I don't remember the link right now, but I have a feeling jsmith will post it here soon.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 14:46:42

quote:
However, I noticed an unexpected result - my results returned included some 'null' values in the QTYONHND column. This is because not every record in IV00101 has a record in IV00102 where LOCNCODE = 'WAREHOUSE'. Is there a way to be certain that the returned value for QTYONHND is '0' if there is no item record with LOCNCODE = 'WAREHOUSE' in IV00102?

Yes, just use the ISNULL function, so replace
IV00102.QTYONHND
with
ISNULL(IV00102.QTYONHND, 0)

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-02-19 : 15:29:57

Thanks to both Peso and snsql for your replies. script works great.

However, as with all great works of art, I would like to expand the view to include one additional column - TotalQTY.

The column I want to add will be a calculation as follows:

(ORDER_QTY + INVOICE_QTY + BACKORDER_QTY + QUOTE_QTY - RETURN_QTY).

I tried modifying my script and adding one line to as follows:

ALTER VIEW tec_item_supply_demand
as
Select IV00101.ITEMNMBR,
IV00101.ITEMDESC,
isnull (IV00102.QTYONHND,0) QOH,
sum(case when SOP10200.SOPTYPE = '2' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' AND SOP10100.VOIDSTTS <> 1 then (QUANTITY*-1) else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as QUOTE_QTY,
isnull (sum (ORDER_QTY + INVOICE_QTY + BACKORDER_QTY + QUOTE_QTY) - (RETURN_QTY),0) as TOTALQTY from IV00101
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR and IV00102.LOCNCODE = 'WAREHOUSE'
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
Group by IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND

I get error message on invalid column names for ORDER_QTY, etc.

If I use this statement:
isnull (sum (QUANTITY),0) as TOTALQTY
i get no erro messages - but the return_qty is considered a positive when I need it to be a negative.

Basically - I am trying to calculate the records that will decrease inventory on hand (quote, backorder, invoice, etc.) but i need to 'add back' the qty being returned since it will increase inventory.

does this make sense?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 15:30:43

quote:
Originally posted by Peso

snSQL, Jeff has an excellent article about why to put the conditions of a JOIN rather in the JOIN than a WHERE, on a LEFT/RIGHT join operation. I don't remember the link right now, but I have a feeling jsmith will post it here soon.

I couldn't find anything in Jeff's blog, perhaps you meant this?
http://www.sqlteam.com/item.asp?ItemID=11122
If so, that's what I was referring to when I said "as happens in the case where you check for nulls" - and on closer inspection this is such a case so it is indeed better to just put those criteria in the JOINs rather than using the IS NULL check that I suggested in the WHERE.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 15:44:01

I'd suggest one of two ways

1. Use a derived table like this (this would be my choice)

ALTER VIEW tec_item_supply_demand
as 
SELECT ITEMNMBR, ITEMDESC, QOH
    , ORDER_QTY, INVOICE_QTY, RETURN_QTY, BACKORDER_QTY, QUOTE_QTY
    , (ORDER_QTY + INVOICE_QTY + BACKORDER_QTY + QUOTE_QTY) - RETURN_QTY as TOTALQTY
(Select IV00101.ITEMNMBR,
IV00101.ITEMDESC,
isnull (IV00102.QTYONHND,0) QOH, 
sum(case when SOP10200.SOPTYPE = '2' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' AND SOP10100.VOIDSTTS <> 1 then (QUANTITY*-1) else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as QUOTE_QTY,
isnull (sum (ORDER_QTY + INVOICE_QTY + BACKORDER_QTY + QUOTE_QTY) - (RETURN_QTY),0) as TOTALQTY from IV00101 
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR and IV00102.LOCNCODE = 'WAREHOUSE'
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE 
Group by IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND) AS dt

2. or use another CASE, like this

ALTER VIEW tec_item_supply_demand
as 
Select IV00101.ITEMNMBR,
IV00101.ITEMDESC,
isnull (IV00102.QTYONHND,0) QOH, 
sum(case when SOP10200.SOPTYPE = '2' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as ORDER_QTY,
sum(case when SOP10200.SOPTYPE = '3' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as INVOICE_QTY,
sum(case when SOP10200.SOPTYPE = '4' AND SOP10100.VOIDSTTS <> 1 then (QUANTITY*-1) else 0 end) as RETURN_QTY,
sum(case when SOP10200.SOPTYPE = '5' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as BACKORDER_QTY,
sum(case when SOP10200.SOPTYPE = '1' AND SOP10100.VOIDSTTS <> 1 then QUANTITY else 0 end) as QUOTE_QTY,
sum(case when SOP10200.SOPTYPE IN ('1', '2', '3', '5') AND SOP10100.VOIDSTTS <> 1 then QUANTITY
    when SOP10200.SOPTYPE = '4' AND SOP10100.VOIDSTTS <> 1 then -QUANTITY
    else 0 end) as TOTALQTY 
from IV00101 
left join IV00102 on IV00102.ITEMNMBR = IV00101.ITEMNMBR and IV00102.LOCNCODE = 'WAREHOUSE'
left join SOP10200 on SOP10200.ITEMNMBR = IV00101.ITEMNMBR
left join SOP10100 on SOP10100.SOPNUMBE = SOP10200.SOPNUMBE 
Group by IV00101.ITEMNMBR,
IV00101.ITEMDESC,
IV00102.QTYONHND

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 01:14:49

snSQL, here is an example what happens when using different filters

DECLARE	@a TABLE (a INT, b INT)

INSERT	@a
SELECT	1, 1 UNION ALL
SELECT	4, 1

DECLARE	@b TABLE (a INT, b INT)

INSERT	@b
SELECT	1, 1 UNION ALL
SELECT	2, 1 UNION ALL
SELECT	4, 2


-- snSQL
SELECT		a.a,
		a.b,
		b.a,
		b.b
FROM		@a AS a
LEFT JOIN	@b AS b ON b.a = a.a
WHERE		b.b = 1 OR b.b IS NULL

-- Peso
SELECT		a.a,
		a.b,
		b.a,
		b.b
FROM		@a AS a
LEFT JOIN	@b AS b ON b.a = a.a AND b.b = 1

This especially happens when filtering over different columns used in the JOIN.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 01:17:02

Putting the filter in the JOIN, filters the table before JOINing.
Putting the filter in the WHERE, filter the complete resultset.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources