sql server programming: Weird LEFT JOIN problem. -- sql server programming

Weird LEFT JOIN problem.

Joel Leong
3/10/2005 11:14:33 PM

sql server programming: I had created a test db below

States: StateID(PK), StateName
Fruits: FruitID(PK), FruitName, GroupId (FK)
FruitState: FruitID (PK), StateID (PK)

All columns is not null.

This query works well with the test db :-

SELECT *
FROM Fruits f
LEFT JOIN FruitState fs ON fs.fruitId = f.FruitID
WHERE f.GroupId = 4
AND ((fs.StateId = 1) OR (fs.StateId IS NULL))

This query will display all the fruits regradless of
where StateId is having the fruits or not. If the fruit
is not available to that state, it will display a null
value.

Sample modified query based on above query

SELECT *
FROM Products p
LEFT JOIN ProductFactory pf ON p.product_id =
pf.product_id
WHERE product_type = 2
AND ((pf.factory_id = 2) OR (pf.factory_id IS NULL))

But, when i issue this query to a production db, this
query will eliminates all the null values. This means if
the products is not availbale to that factory, the row is
no longer displayed.

What is the reason? No matter I use left or right join,

Weird LEFT JOIN problem.

Joel Leong
3/10/2005 11:46:50 PM

Just figured out I forgot the filter in the join:
fs.StateId= 1

USE MyDBTest1
go

SELECT *
FROM Fruits f
LEFT JOIN FruitState fs ON (fs.fruitId = f.FruitID AND
fs.StateID = 1)
WHERE f.GroupId = 1
AND ((fs.StateId = 1) OR (fs.StateId IS NULL))

[quoted text, click to view]

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 programming group.