need help with quite an advanced SQL query.

[xconspirisist]

I think this is going to be pretty hard to explain - but i'll try and give as much detail as possible.

I need to rip specific items from two tables in a database, proberbly at the same time.

The tables are constructed as so :

sql table : store_items
sql table : store_inventory

now, I need to get all items from store_items where the item_type field is 'computer', so the following query should be fine ?:

SELECT * FROM store_items WHERE item_type = 'computer';

However, the store_items contains information, irionicly, about items.

record 1 :
item_id : 1
item_name : fred
item_type : computer
item_size : 3
etc, etc.

record 2 :
item_id : 2
item_name : barney
item_type : computer
item_size : 2
etc, etc.

record 3 :
item_id : 3
item_name : dynage
item_type : computer
item_size : 44
etc, etc.

The table : store_inventory contains information, about how many of each item, a user has. the store_inventory table looks somthing like this :

record 1 :
inventory_id : 66
inventory_item_owner : 2
inventory_item_id : 1

record 2 :
inventory_id : 13
inventory_item_owner : 2
inventory_item_id : 2

record 3 :
inventory_id : 99
inventory_item_owner : 2
inventory_item_id : 3

As you may see, the the inventory_item_id refurs to the item_store table.

I now need all the items where inventory_item_owner = 2, for example, so the query would look something like this :

SELECT * FROM store_inventory WHERE inventory_item_owner = 2;

but, I need the item_name that the inventory_id is refuring to.

Hope that makes sense.

[lilleman]

Try this:

SELECT t2.*, t1.name FROM store_items AS t1, store_inventory AS t2
WHERE t2.inventory_item_owner = 2 AND t2.inventory_item_id = t1.item_id

[xconspirisist]

Im having massive sucess, and problems with the code you've posted.

When I delare [table]AS[blah], thats fine, and sql returns the correct results. however - when I specify a second [table]AS[blah], it appears to return every single record from within the table, which I find most odd. even when only declaring the second [table]AS[blah] - and not actually using the [blah].[field] from the query, it still acts as before - giving me every result within the datase - also each record has the same inventory_user value to what is specified in the query.

SELECT table1.inventory_user, table2.item_name FROM phpbb_store_inventory AS table1, phpbb_store_items AS table2 WHERE table1.inventory_user =22

I've renamed some fields, I hope it doesnt complicate the process too much - most of the new field names are obvious.

I appologise i my explination is, well, crap - but i cant think of how else to explain it - I thank anyone in advance that offers even the smallest bit of help, many many thanks guys.

[xconspirisist]

• el bumpo *

Can anybody help me with this ?

[lilleman]

Hm, try this. Not sure it will work better but at least it's worth a try.

SELECT table1.inventory_user, table2.item_name
FROM phpbb_store_inventory AS table1
LEFT JOIN phpbb_store_items AS table2
ON table1.item_id = table2.inventory_item_id
WHERE table1.inventory_user =22