Learning to use Advanced features of SQL

Question

I'm at the point where i need to learn to use more advanced features of SQL if i would like to advance my career. I confess i don't know what a join is or how to use them. i start reading queries with joins and my brain just turns to mush, but i have encountered a situation where i might benefit from using a join, but i don't know if what i want to do is even possible.

consider the following tables:

user

id  | user_name | location_id
===============================
1   | r3wt      | 316

location

id  | state | city
=========================
316 | Clarksville | Arkansas

Now when i select the user, i get back an array like

[ 
    id => 1, 
    user_name => r3wt , 
    location_id =>316
]

What i would like to get back is:

[ 
    id => 1, 
    user_name => r3wt , 
    location_id =>[
        id=>316,
        city=>Clarksville,
        state=>Arkansas
    ]
]

I'm wondering if this is possible, and if so how i might alter my quite bland select query to make this possible. Thanks, and if you need more details or want me to make what will be a pathetic attempt at figuring it out myself, i am willing to embarrass myself to learn. thank you

My pathetic little attempt which of course doesnt work:

SELECT id, user_name, location AS location_id
FROM user JOIN location ON location.id = user.location_id

Keep in mind i have absolutely no idea what i'm doing. i don't even understand what joins are and how they work, but i understand what i would like to be able to do with SQL.

Answer 1

You should:

SELECT u.id, u.name, u.location_id, l.city, l.state
 FROM  user u    -- identify columns from user table with 'u.'
 LEFT OUTER JOIN location l -- identify columns from location with 'l.'
   ON u.location_id = l.id --- the join predicate when the two ids match 
 WHERE l.state = 'Arkansas' -- other selection criteria

This will give you almost what you wanted but as a single flat array:-

[ 
    u.id => 1, 
    u.user_name => r3wt , 
    u.location_id => 316,
    l.city=>Clarksville,
    l.state=>Arkansas
]

SQL results are always a two dimensional table. This is fundamental to the way SQL works! The "LEFT OUTER" means you want null values for city and state in the result if no location is present.

Just google "MYSQL TUTORIAL" and you will find dozens of sites offering on-line guides for free. Cannot recommend a particular one as I learnt all this stuff in pre-history.

Answer 2

Joins are used to combine 2 or more tables to get results from them all. There are several types of Joins and you can read about them here: http://www.w3schools.com/sql/sql_join.asp

It is important to remember that MySql return rows so in your example if for the discussion a user can have several locations you will not be using join - or if you do use joins you will have several rows of the user id and the different locations he have.

In your example, for a user that have one row of location you can run this query

SELECT *
FROM user
INNER JOIN location
ON user.location_id=location.id;

This query will return you rows for all users that have a location record and you will get all the fields of both tables (as we used *).