Left Outer 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

SQL Server 2005 Forums

Transact-SQL (2005)

Left Outer Join problem

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2010-02-19 : 14:46:04

I have the following query (double-quotes added by Crystal Reports) which isn't returning the results I expect:


 SELECT "INLOC"."Itemclasskey", "INLOC"."Itemkey", "INLOC"."Locationkey", "INLOC"."Qtyonhand", "INLOC"."Avgcost", "INMAST"."Itemdescription1",
"INMAST"."Commoditykey", "INCLASS"."Classdescription", "INTXDH"."Postdate", "INTXDH"."Transactionqty", "INTXDH"."Transactiontype",
"INTXDH"."Documentnumber", "INTXDH"."Incrementdecrement", "INLOC"."Qtyonhand" * "INLOC"."Avgcost" as "Itemvalue"
 FROM  "INLOC" "INLOC"
LEFT OUTER JOIN "INMAST" "INMAST" ON "INLOC"."Itemkey"="INMAST"."Itemkey" 
LEFT OUTER JOIN "INCLASS" "INCLASS" ON "INLOC"."Itemclasskey"="INCLASS"."Itemclasskey" 
LEFT OUTER JOIN "INTXDH" "INTXDH" ON ("INLOC"."Itemkey"="INTXDH"."Itemkey") AND ("INLOC"."Locationkey"="INTXDH"."Locationkey")
WHERE  "INLOC"."Itemclasskey" IN ('CONT','FG','RM') 
ORDER BY "INLOC"."Itemclasskey", "Itemvalue", "INLOC"."Itemkey", "INLOC"."Locationkey", "INTXDH"."Postdate", "INTXDH"."Documentnumber"

I expected to get every item/location combo in "INLOC" regardless of whether or not it had any matching entries in "INTXDH". I am not. I am only getting the combos from "INLOC" which have records in "INTXDH". Also, even though I am using LEFT OUTER JOINS to link to "INMAST" and "INCLASS", every record in "INLOC" will have matching entries in "INMAST" and "INCLASS".

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 14:53:41

Can you show some sample data from INLOC and INTXDH tables and tell us which records from INLOC you expect in the output....but not showing up.

SBLatta
Starting Member

33 Posts

Posted - 2010-02-19 : 15:07:50

I hope this lines up...

INLOC

Itemkey Locationkey Qtyonhand Avgcost Itemclasskey
R00200 Bin001 170 1.25 RM
R00240 Bin001 150 .95 RM
R00243 Bin001 27 1.50 RM

INMAST

Itemkey Itemdescription1
R00200 Purple Widget
R00240 Green Widget
R00243 Red Thingy

INCLASS

Itemclasskey Classdescription
RM Raw Material
FG Finished Good
Cont Container

INTXDH

Itemkey Locationkey Postdate Transactionqty Transactiontype Incrementdecrement Documentnumber
R00200 Bin001 02/11/2010 200 APU 1 PO7762
R00200 Bin001 02/11/2010 30 ASA -1 INV-002330
R00243 Bin001 12/27/2010 40 APU 1 PO7789
R00243 Bin001 12/31/2010 13 ASA -1 INV-002544

Expected Results:

Itemclasskey Itemkey Locationkey Itemdescription1 Qtyonhand Avgcost Postdate Transactionqty Transactiontype Incrementdecrement Documentnumber Classdescription Itemvalue
RM R00200 Bin001 Green Widget 170 1.25 02/11/2010 200 APU 1 PO7762 Raw Material 212.50
RM R00200 Bin001 Green Widget 170 1.25 02/11/2010 30 ASA -1 INV-002330 Raw Material 212.50
RM R00240 Bin001 Purple Widget 150 .95 Raw Material 142.50
RM R00243 Bin001 Red Thingy 27 1.50 12/27/2010 40 APU 1 PO7789 Raw Material 40.50
RM R00243 Bin001 Red Thingy 27 1.50 12/31/2010 13 ASA -1 INV-002544 Raw Material 40.50

As you can see, Itemkey R00240 has no transactions in "INTXDH", yet because of the left outer join, I was expecting it to be included in the result set. I'm not sure how else to structure the query to get the results I want.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 15:19:44

Is it beacuse of the WHERE clasue filtering out the ItemClassKeys?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:28:30

Yeah..not to mention...you haven't provided that column in the sample output..so there's no way for us to tell.

SBLatta
Starting Member

33 Posts

Posted - 2010-02-19 : 16:04:07

I didn't include it in the sample data because I'm sure that WHERE clause isn't the problem. I just removed the WHERE clause entirely and reran the query with exactly the same results. However, if it'll make things easier, I'll add the missing columns to the sample data.

Edit: Ok, I think all of the columns of sample data are there now, although they may not be in the same order as they are in the query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 16:26:01

Well your query doesn't match your sample data. The ItemClassKey is in the INCLASS table for te sample data, but in the query it's in the INLOC table.

Also, please refer to this link for how to prepare your DDL and DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SBLatta
Starting Member

33 Posts

Posted - 2010-02-19 : 16:51:32

Itemclasskey is in both tables. It's the field the two tables are linked on.

As far as the DDL and DML are concerned, I read the info you linked to and I don't have Enterprise Manager. I have an ODBC connection to the SQL server, and I do all my work in Excel or Crystal Reports. The best I'm able to do is copy my query from one of those two places. And I never, ever have the luxury of working with tables I created, so even if I had Enterprise Manager, I'm not sure I could post CREATE or INSERT queries.

I don't know what else I can do to make the questions I post easier to read and understand. To me, aside from the order the fields are in, the sample results I posted are exactly what I would expect the query to create. Unfortunately, I can't post results taken out of the actual data - that would get me fired really quickly. Every time I come here to ask a question and I get asked for sample data, I have to make up sample results. I base it on the real results, but I can't use real results.

I have tried the query again, removing the WHERE clause and all references to INTXDH. The query results from what is left is what I expected... one row for every Itemkey/Location combination in the INLOC table. The problem has to be happening when INTXDH is added to the query.

Any ideas would be appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 17:10:31

I'm not sure what to tell you. The query you posted looks pretty straight forward. who knows what crystal reports is doing. You could try running these two queries and see if there is a difference:

SELECT COUNT(*)
FROM INLOC 
WHERE Itemclasskey IN('CONT', 'FG', 'RM')

SELECT COUNT(*)
FROM
	INLOC INLOC
LEFT OUTER JOIN
	INTXDH INTXDH
	ON INLOC.Itemkey = INTXDH.Itemkey
	AND INLOC.Locationkey = INTXDH.Locationkey
WHERE
	INLOC.Itemclasskey IN ('CONT', 'FG', 'RM')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 17:25:08

As far as I can tell the sample code is producing the correct results. I've posted the DDL, DML and query per your sample:

DECLARE @INLOC TABLE 
(
	Itemkey VARCHAR(10), 
	Locationkey VARCHAR(10), 
	Qtyonhand INT, 
	Avgcost DECIMAL(10,4), 
	Itemclasskey VARCHAR(5)
)

INSERT @INLOC
SELECT 'R00200', 'Bin001', 170, 1.25, 'RM'
UNION ALL SELECT 'R00240', 'Bin001', 150, .95, 'RM'
UNION ALL SELECT 'R00243', 'Bin001', 27, 1.50, 'RM'

DECLARE @INMAST TABLE (Itemkey VARCHAR(10), Itemdescription1 VARCHAR(50))

INSERT @INMAST
SELECT 'R00200', 'Purple Widget'
UNION ALL SELECT 'R00240', 'Green Widget'
UNION ALL SELECT 'R00243', 'Red Thingy'

DECLARE @INCLASS TABLE (Itemclasskey VARCHAR(5), Classdescription VARCHAR(50))

INSERT @INCLASS
SELECT 'RM', 'Raw Material'
UNION ALL SELECT 'FG', 'Finished Good'
UNION ALL SELECT 'Cont', 'Container'


DECLARE @INTXDH TABLE 
(
	Itemkey VARCHAR(10),
	Locationkey VARCHAR(10),
	Postdate DATETIME,
	Transactionqty INT,
	Transactiontype VARCHAR(10),
	Incrementdecrement INT,
	Documentnumber VARCHAR(20)
)
INSERT @INTXDH
SELECT 'R00200', 'Bin001', '02/11/2010', 200, 'APU', 1, 'PO7762'
UNION ALL SELECT 'R00200', 'Bin001', '02/11/2010', 30, 'ASA', -1, 'INV-002330'
UNION ALL SELECT 'R00243', 'Bin001', '12/27/2010', 40, 'APU', 1, 'PO7789'
UNION ALL SELECT 'R00243', 'Bin001', '12/31/2010', 13, 'ASA', -1, 'INV-002544'


SELECT
	INLOC.Itemclasskey,
	INLOC.Itemkey,
	INLOC.Locationkey,
	INLOC.Qtyonhand,
	INLOC.Avgcost,
	INMAST.Itemdescription1,
	--INMAST.Commoditykey,
	INCLASS.Classdescription,
	INTXDH.Postdate,
	INTXDH.Transactionqty,
	INTXDH.Transactiontype,
	INTXDH.Documentnumber,
	INTXDH.Incrementdecrement,
	INLOC.Qtyonhand * INLOC.Avgcost AS Itemvalue
FROM
	@INLOC INLOC
LEFT OUTER JOIN
	@INMAST INMAST
	ON INLOC.Itemkey = INMAST.Itemkey
LEFT OUTER JOIN
	@INCLASS INCLASS
	ON INLOC.Itemclasskey = INCLASS.Itemclasskey
LEFT OUTER JOIN
	@INTXDH INTXDH
	ON INLOC.Itemkey = INTXDH.Itemkey
	AND INLOC.Locationkey = INTXDH.Locationkey
WHERE
	INLOC.Itemclasskey IN('CONT', 'FG', 'RM')
ORDER BY
	INLOC.Itemclasskey,
	Itemvalue,
	INLOC.Itemkey,
	INLOC.Locationkey,
	INTXDH.Postdate,
	INTXDH.Documentnumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources