CODEWITHSUNDEEP
phpmysql
popClingwrap
popClingwrap

Reputation: 4217

MySQL Query - Basic joining advice

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

Answers (3)

Sridhar
Sridhar

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

Chris
Chris

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

cms_mgr
cms_mgr

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

Related Questions