Reputation: 4217
I literally started learning PHP / MySQL yesterday so I apologies if this is a simple/daft/badly phrased question.
I have a db set up with two tables:
Locations
l_id | loc_name
----------------
1 | France
2 | London
3 | The Moon
Items
i_id | item_name | item_loc
----------------------------
1 | Book | 1
2 | Mirror | 1
3 | Bread | 2
4 | Camera | 2
5 | Cheese | 3
6 | Ladle | 3
When supplied with a list of l_id numbers how can I best find the loc_name and all the items assigned to each?
Can this be done with a single query or do I need to query twice - once for the name and once for the items?
On a side not, I have had some very minimal success using JOIN in other queries but I don't really understand why they are working. I'm having a hard time getting it settled in my head exactly what the process is that behind the scenes and how the syntax describes it. If anyone can recommend any reading matter on the subject that would be great.
Thank you for any help and sorry if the question is a bit
Upvotes: 0
Views: 73
Reputation: 59
Since l_id and item_loc are the same attribute we can perform inner join on both the tables.
select l.loc_name,i.item_name
from Items i inner join Locations l
on i.item_loc=l.l_id
Basically it'll combine both tables by mapping the common attribute values (here item_loc and l_id). We get another table with attributes(i_id,item_name,item_loc,loc_name).
Upvotes: 0
Reputation: 518
Should be working:
SELECT items.i_id, items.item_name, location.loc_name FROM items LEFT JOIN locations ON l_id = items.item_loc
Upvotes: 1
Reputation: 2017
In terms of reading material Learning SQL from O'Reilly is a great place to start. If you'd prefer a free resource then the W3Schools SQL tutorial introduces the basics nicely. I find using the fuller syntax with explicit joins makes it easier to understand what's happening. So, in your example, something like:
SELECT *
FROM locations AS loc
INNER JOIN items AS i
ON loc.l_id = i.item_loc
WHERE loc.l_id IN (value1,value2,...)
Upvotes: 1