sql server mseq: Left Outer Join Only Including Matching Rocords -- sql server mseq

Left Outer Join Only Including Matching Rocords

Stan
6/22/2007 12:31:02 PM

sql server mseq: I am executing the following SQL statement in Management Studio in a SQL
Server 2005 environment:
Select
wl.wmslocationid,il.itemid,il.citullcastartpickinglocation,il.citullcaendpickinglocation
from wmslocation wl left outer join inventitemlocation il
on wl.wmslocationid = il.citullcastartpickinglocation

The returned set of records ONLY shows the records that have matching
entries in both tables. The records from the 'left' table are dropped from
the result. This is not behaving the way a 'left outer' should behave.
--

RE: Left Outer Join Only Including Matching Rocords

Mohit K. Gupta
6/22/2007 3:49:23 PM

Try running these two queires:

--QueryA:
Select Distinct wmslocationid , COUNT(*)
FROM wmslocation

--QueryB:
Select Distinct citullcastartpickinglocation, COUNT(*)
FROM inventitemlocation

If the count value match in QA and QB, then the result you are getting is
right. There aren't any extra records in wmslocation that don't exist in
inventitemlocation.

Maybe I am misunderstanding your tables from the SQL Statement, anyhow
hopefully that helps.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005

[quoted text, click to view]

Re: Left Outer Join Only Including Matching Rocords

Hugo Kornelis
6/23/2007 12:50:13 AM

[quoted text, click to view]

Hi Stan,

If this is the *complete* query you are sending, then you might have run
into a bug - though it would surprise me, since this is a fairly regular
kind of query and bugs usuallly only appear in obscure queries that are
not in the standard testet of MS and seldom used in the real world.

I suspect that there is another explanation - but to find it, I need to
be able to reproduce the problem. For that, I need a SQL script
containing the CREATE TABLE statements to reproduce your tables,
including all constraints, properties, and indexes; some INSERT
statements with a few well-chosen rows of sample data; the *EXACT* query
you are executing: and both the expected and real output.

I'd also like to know what version of SQL Server you have running.
Please post the result of
SELECT @@VERSION

--
Hugo Kornelis, SQL Server MVP

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.