Left join with is null - 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

Transact-SQL (2000)

Left join with is null

Author

Topic

GunYan
Starting Member

3 Posts

Posted - 2004-07-27 : 05:52:50

Hi all!

I have a Warehouse table and a table showing stockinformation for each warehouse and each product. Sometimes one warehouse don't have a certain article in the stocktable and tha't when i get my problem.

Everything works fine when i only have one product in the Stock table but when i populate my stocktable with other products the rows with Null values on the right disappears. Do anyone have a solution for this. I've been trying for hours to find one... :-( I need all the warehouses on the left even if there are no matching rows on the right.

Thanx in advance!

SELECT *
FROM
Warehouse
LEFT OUTER JOIN
StockSum ON StockSum.WarehouseNr = Warehouse.WarehouseNumber AND StockSum.Site = Warehouse.Site
WHERE
(Warehouse.Site = 'siba.se') AND (StockSum.ProductNr = 'SEPC281' OR
internetuser.Lagersaldo.ProductNr IS NULL)

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-27 : 06:04:16

(Warehouse.Site = 'siba.se') -- This will limit the result to just one warehouse regardless
AND
(StockSum.ProductNr = 'SEPC281' OR
internetuser.Lagersaldo.ProductNr IS NULL) -- Not sure why you are including a totally different table at this point

-- The last bit should probably be

(StockSum.ProductNr = 'SEPC281' OR StockSum.ProductNr IS NULL)

-------
Moo. :)

GunYan
Starting Member

3 Posts

Posted - 2004-07-27 : 06:48:52

Sorry for not being clear enough. I translated the column and table names from swedish but apparently i forgot some.

Site can have five different values but in this case it's 'siba.se'. Around 50 warehouses share the same sitename. In this question i only want warehouses that have the site 'siba.se'. Here it's the right translation. My problem is still unsolved. Thanx anyway!

SELECT *
FROM
Warehouse
LEFT OUTER JOIN
StockSum ON StockSum.WarehouseNr = Warehouse.WarehouseNumber AND StockSum.Site = Warehouse.Site
WHERE
(Warehouse.Site = 'siba.se') AND (StockSum.ProductNr = 'SEPC281' OR
Stocksum.ProductNr IS NULL)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-27 : 07:16:37

The problem here is that when you add a WHERE condition on the insignificant table in a left join (i.e. the right table), SQL Server treats this as an INNER JOIN rather than a LEFT JOIN. The solution is simply to move any WHERE conditions on the StockSum table to the JOIN predicate. So your statement would be:


SELECT *
FROM Warehouse
LEFT OUTER JOIN StockSum 
ON StockSum.WarehouseNr = Warehouse.WarehouseNumber AND StockSum.Site = Warehouse.Site
AND (StockSum.ProductNr = 'SEPC281' OR Stocksum.ProductNr IS NULL)
WHERE (Warehouse.Site = 'siba.se')

GunYan
Starting Member

3 Posts

Posted - 2004-07-27 : 07:54:23

Thanx a lot! That solved it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources