Where clause matching

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

try this code:

Code:

---

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

---

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

Code:

---

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:

Code:

---

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:

Code:

---

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.

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

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

sql Code:

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

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!

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