Where clause matching

[enex]

how can I return a list of all the rows in the table including null values, as my where clause matching is only true if the value exists in both tables

SELECT * FROM Items, Stock WHERE Items.StocksId=Stock.StocksId

the Items table could have a null value for the StocksId so how do I return these values too!

thanks in advance
enex

[nubie]

try this code:

SELECT * FROM items LEFT JOIN stock	ON items.StockId = stock.StockId

[enex]

Hi this works well, now I have a requirement to match on ResultsId too! in the items table so I did the following

SELECT * FROM items WHERE ResultsId=1 LEFT JOIN stock ON items.StockId = stock.StockId

but I go the following error:
Incorrect syntax near the keyword 'left'.

How can I match on the results Id too?

edit this appears to work:

SELECT * FROM items, ItemLevel  LEFT JOIN stock ON items.StockId = stock.StockId WHERE ResultsId=1

but now Im trying to match on multiple tables where I know values do exist like this:

SELECT * FROM items, ItemLevel  LEFT JOIN stock ON items.StockId = stock.StockId WHERE ResultsId=1
AND Items.ItemLevelID=ItemLevel.ItemLevelId

but I get this error:
The multi-part identifier "ItemLevel.ItemLevelId" could not be bound.

[jmcilhinney]

The WHERE clause comes after the FROM clause and the JOIN is part of the FROM clause.

[GaryMazzone]

You are mixing forms here also. ItemLevel is in the FROM clause (old standard). It should be written like this:

SELECT 
   * 
FROM 
   items
INNER JOIN ItemLevel  
   ON Items.ItemLevelID=ItemLevel.ItemLevelId
LEFT OUTER JOIN stock 
   ON items.StockId = stock.StockId 
WHERE ResultsId=1

[enex]

Hi Jmc thanks for the reply, I have edited my code to work as you suggested, this works well, I have edited my above post can you help me with my other query?

thanks in advance
enex

EDIT: thanks gary will give it a go!

[GaryMazzone]

Also change Left Join to LEFT OUTER JOIN I missed that on my first look