SIMPLE MySQL Join question...

Question

Hello again!

I'm a complete newbie to SQL, and I am having problems with what I assume is an easy easy SQL join statement.

I have an "inventory" and an "items" table. The "inventory" table contains a user_id, an item_id, and a qty. The "items" table contains an item_id and and item_name.

I want a user to get a list of all inventory items listed in the "inventory" table with their user_id, and then in each row, display the item_name from the "items" table associated with the item_id in the "inventory" table...

It seems like it should be really simple, but all my queries seem to be returning every item in the "items" table rather than every item in the "inventory" table. Could someone give me an example of the SQL query I should be using?

Answer 1

select i.user_id, i.item_id, i.qty, it.item_name
from inventory i
inner join items it on i.item_id = it.item_id
where i.user_id = 42

Update:

If it is not guaranteed that there are matching records in the items table, you may need to use a left outer join, like this:

select i.user_id, i.item_id, i.qty, it.item_name
from inventory i
left outer join items it on i.item_id = it.item_id
where i.user_id = 42

Answer 2

Here, try this:

select inventory.user_id, items.item_name
from inventory
inner join items on inventory.item_id = items.item_id
where inventory.user_id = `$some_user_id`;

Answer 3

SELECT inventory.*, items.* FROM inventory, items WHERE inventory.user_id='$userid' AND inventory.item_id = items.item_id