Wow, reading my post again I realized I made a huge mistake!!
Those JOINS are useless if we want to retrieve ONLY the phone numbers (or emails).
The correct query would be simply:
Code:
SELECT number FROM phones WHERE contact_id = 8;
Now let's get back on the JOIN.
As you can see, in my example I used three querys to get the information I wanted, the first one was used to get the main contact information, and the second and third were used to get the phones and emails. This isn't a very good practice because using a JOIN we can get all that information at once.
Joins are used when your data is scattered through multiple tables and you want to return them all at once. To be able to join tables you need to tell the database engine to look for similar data in the tables being joined, in our case this data is the contact_id field.
The basic syntax of a JOIN is like this:
Code:
SELECT
<fields>
FROM
<main_table>
LEFT JOIN
<joined_table>
ON
<main_table>.<join_field> = <joined_table>.<join_field>
WHERE
<condition>;
If we want to retrieve our contact's name, address and phone numbers:
<fields> = name, address, number
Our main table is the contacts table, so:
<main_table> = contacts
The table being joined is the phones table:
<joined_table> = phones
The field we're using to make the join is the contact_id field:
<join_field> = contact_id
And since we want only the data from contact #8:
<condition> = contacts.contact_id = 8;
Putting everything together:
Code:
SELECT
name, address, number
FROM
contacts
LEFT JOIN
phones
ON
contacts.contact_id = phones.contact_id
WHERE
contacts.contact_id = 8;
If we wanted to get the emails as well, all we need to do is attach another join after the first one, like this:
Code:
SELECT
name, contacts.address, emails.address, number
FROM
contacts
LEFT JOIN
phones
ON
contacts.contact_id = phones.contact_id
LEFT JOIN
emails
ON
contacts.contact_id = emails.contact_id
WHERE
contacts.contact_id = 8;
Notice how I changed the <fields> part to contacts.address and emails.address. This is needed because these fields have the same name, since we want to return both we need to tell this explicitly to the database or else our query will fail. In the first example we weren't joining the emails table, so the only 'address' field that the database engine sees is the contacts.address field, so there's no need to explicitly tell the database that the address field we want is the one in the contacts table.
Another situation where a JOIN can make your life easier is when you have many-to-one relations. Suppose you're creating an application to catalog your DVDs, every DVD will have a title, duration and genre.
Code:
----------
|dvds |
----------
|dvd_id |
|title |
|duration|
|genre |
----------
If you store the genre as text every Action movie will have the string "Action" stored in that field. If tomorrow you decide that this genre should be renamed to "Action/Adventure" you'll need to update all the records in your database.
In this case we have a many-to-one relation, because one genre can be associated with many DVDs, but each DVD can only have one genre. In this case it's smarter to store the genre in a separate table that works like an index. Changing our design:
Code:
---------- ------------
|dvds | |genres |
---------- ------------
|dvd_id | |genre_id |
|title | |genre_name|
|duration| ------------
|genre_id|
----------
The genres table works like an index:
- Action
- Comedy
- Musical
Notice how I changed the dvds table to have a genre_id field. This field will have the same value as the genre_id field in the genres table. This way, if you have an Action movie you only need to store the number 1 in the genre_id in the dvds table. If you decide to change the genre_name from "Action" to "Action/Adventure" you only need to do this in the genre table, all dvds that had genre_id = 1 have now virtually changed to "Action/Adventure".
Now let's suppose you want to produce a list of your DVDs showing their titles, duration and the genre. You can do this by retrieving all the dvds first, and then for each one of them you query the genre table to retrieve the genre. However, as we already know, this is NOT smart!! Both our tables have a genre_id field, so we can use it to JOIN the tables and retrieve all the information we want with just one query.
Here comes the JOIN again.
<fields> = title, duration, genre_name
<main_table> = dvds
<joined_table> = genres
<join_field> = genre_id
<condition> = none, because we are retrieving all records
Put everything in the query and we have:
Code:
SELECT
title, duration, genre_name
FROM
dvds
LEFT JOIN
genres
ON
dvds.genre_id = genres.genre_id;
And this concludes this tutorial-post.
I won't recommend any books to you 'cause I simply don't know anyone, I never really needed it. I learned the concept all by myself after some trial and error, reading other tutorials around the net and asking in forums like you're doing.
Maybe the more experienced guys can suggest a book for you, but I'm confident you can learn it all by yourself too, it just needs a little dedication.
