Using Joins - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Using Joins

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-03-27 : 17:13:49

Hi,

I am writing a query which uses joins but the results are not correct. I am pulling data from an item master table (IV00101 and Item_Mapping) and 2 transaction tables, (POP10500 and IV30300) which I have in separate sub-queries. The data from POP10500 is in subquery D3 and the data from IV30300 is in subquery D2.

The problem is that when there is no data in IV30300, the query does not populate the fields from POP10500. I have tried using different joins but I still cannot get it o work.

Can anyone help ?

INSERT INTO INVENTRY([STOCKLIST CODE], [WAREHOUSE CODE], [PROCESS YYYYMM], [INVENTORY TYPE], [PRODUCT CODE], [OPENING BALANCE QTY.], [OPENING BAL. VALUE],[RECEIPT QUANTITY],[RECEIPT VALUE],[FREE RECEIPT QUANTITY],[FREE RECEIPT VALUE],[RETURN QUANTITY],[RETURN VALUE],[ADJUSTMENT QUANTITY],[ADJUSTMENT VALUE],[ALLOCATED QUANTITY],[ALLOCATED VALUE],[CLOSING QUANTITY],[CLOSING VALUE])

SELECT '5555550101'
,LocnCode
,ProcessDate
,'S'
,ProductCode
,OpenQty
,OpeningBalValue
,ISNULL(RecQty,0.00)
,ISNULL(RecValue,0.00)
,0.00
,0.00
,0.00
,0.00
,ISNULL(AdjQty,0.00)
,ISNULL(AdjValue,0.00)
,AllocQty
,AllocValue
,ClosingQty
,ClosingValue

FROM
(
SELECT
LEFT(I3.LOCNCODE,6) as LocnCode
,LTRIM(STR(YEAR(GETDATE())) + REPLACE(STR(MONTH(GETDATE()), 2),' ', '0')) As ProcessDate
,I2.[CP PRODUCT CODE] as ProductCode
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As OpenQty
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 As OpeningBalValue
,SUM(I3.ATYALLOC*I2.[UNITS/CASE]) As AllocQty
,SUM(I3.ATYALLOC*I1.CURRCOST*I2.[UNITS/CASE])/71.70 As AllocValue
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As ClosingQty
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 As ClosingValue

FROM IV00101 I1
JOIN [ITEM_MAPPING] I2
ON I1.ITEMNMBR = I2.[KIRK ITEM NUMBER]
JOIN IV00102 I3
ON I1.ITEMNMBR = I3.ITEMNMBR
AND I3.LOCNCODE = 'MGD-MAIN'

GROUP BY I2.[CP PRODUCT CODE], LEFT(I3.LOCNCODE,6)
)D1

LEFT JOIN
(
SELECT
I6.[CP PRODUCT CODE] as ProductCode2
,LTRIM(STR(YEAR(GETDATE())) + REPLACE(STR(MONTH(GETDATE()), 2),' ', '0')) As ProcessDate2
,MONTH(I5.DOCDATE) as DocDate
,SUM(I5.TRXQTY*I6.[UNITS/CASE]) as AdjQty
,SUM(I5.EXTDCOST/71.70) as AdjValue
FROM IV30300 I5
INNER JOIN [ITEM_MAPPING] I6
ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR
AND MONTH(I5.DOCDATE) = '2'
GROUP BY I6.[CP PRODUCT CODE], MONTH(I5.DOCDATE)
)D2
ON D1.ProductCode = D2.ProductCode2
and D1.ProcessDate = D2.ProcessDate2
LEFT OUTER JOIN
(
SELECT
I9.[CP PRODUCT CODE] as ProdCode
FROM [ITEM_MAPPING] I9
JOIN IV30300 I10
ON I9.[KIRK ITEM NUMBER] = I10.ITEMNMBR
GROUP BY I9.[CP PRODUCT CODE]
)D4
ON D2.ProductCode2 = D4.ProdCode
LEFT OUTER JOIN
(
SELECT
MONTH(P1.DATERECD) as RecDate
,I7.[CP PRODUCT CODE] as ProductCode2
,P1.TRXLOCTN as Loc
,SUM(P1.QTYSHPPD * I7.[UNITS/CASE]) as RecQty
,SUM(P1.QTYSHPPD * I7.[CASE PRICE]) as RecValue

FROM POP10500 P1
INNER JOIN [ITEM_MAPPING] I7
ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER]
AND TRXLOCTN = 'MGD-MAIN'
AND MONTH(P1.DATERECD) = '2'
GROUP BY I7.[CP PRODUCT CODE], P1.TRXLOCTN, MONTH(P1.DATERECD)
)D3
ON D4.ProdCode = D3.ProductCode2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-27 : 19:02:34

You need to use an OUTER JOIN to fix the issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-03-28 : 15:56:59

Are you referring to a left outer join or a right outer join ? Where would I need to use it to resolve the problem ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 16:32:53

That depends on your requirement. To get complete soln,post your table ddls & also your full requirement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-28 : 16:56:25

It depends on what order you put them into the query.

Here's an example with a LEFT OUTER JOIN with aliases to help you understand:

FROM POP10500 lefttable
LEFT JOIN IV30300 righttable ON lefttable.PK = righttable .PK

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources