Select Join Query 1

[sipps]

Hi all,

I have a query, and I know I should be able to solve it using an outer join, but I cant get it to work, can someone help!

The three tables I'm pulling data back from are item, category and stock. The item table holds details about the item, and it is linked to category by Category_ID. The category table just holds what type of item it is, and a general description about that categry. The stock table just holds the Item_ID and a Quantity value.
I want to bring back all these details about this item, even when there is none in stock (quantity is null).

I wrote this query so far:
SELECT *
FROM item, category
WHERE category.Category_ID = item.Category_ID

And it brings back the item with the correct category name etc. Then I went on to get the stock quantity, knowing that some items were not in stock:
SELECT *
FROM item, category LEFT OUTER JOIN stock ON item.Item_ID = stock.Item_ID
WHERE category.Category_ID = item.Category_ID

This brings back a whole list of values, which are incorrect, please can someone help me sort this query out!

Thank you

 

[shadedecho]

I think if I understand your problem correctly, you can just use the default join that MySQL assumes when you use "," between table names... so:

SELECT * FROM item, category, stock WHERE category.Category_ID = item.Category_ID AND item.Item_ID = stock.Item_ID

 

[swampBoogie]

That query should be okay, seems like an error in Mysql if you are correct.

You could try

SELECT *
FROM item inner join category
on category.Category_ID = item.Category_ID
LEFT OUTER JOIN stock ON item.Item_ID = stock.Item_ID

 

[sipps]

The problem comes when there are no values in the stock table for that item, or that item does not appear in the stock table as yet, for whatever reason. So, the query should list all the items regardless of whether there is stock or not. I know this is a join, but I cannot get it to work!

Thank you for your help

sipps

 

[sipps]

swampboogie,

your query worked! I just had to specify the item.Item_ID in the select part of the query, because it would only bring back the Item_ID's of items that appeared in the stock table, thank you very much!!!

sipps