She really can fly you know
Mastering the table join
In this tutorial I try to explain one of the most simplest and useful mechanisms available to you in a relational SQL database: the standard Join (also called Inner Join).
Querying the Tables
To connect these tables together we need to do a query. More specifically a query with a ‘join’ between the tables. The ‘join’ describes how we wish to relate the tables together.
As a simple example, consider you wanted to list all of the babes from the club. You could simply do this with the following query:
select name from surfer where gender_id=1;
This does the job, but it relies on us knowing the unique id of the gender ‘babe’ (which is ‘1′). A join allows us to accomplish this this using the gender table as a lookup.
select s.name, g.gender from surfer as s join gender as g where s.gender_id=g.id and g.gender="Babe";
Returns:
+--------+--------+ | name | gender | +--------+--------+ | Sheila | Babe | | Kylie | Babe | | Danni | Babe | +--------+--------+
This SQL statment introduces a number of syntax elements. The tables gender and surfer are aliased. e.g. “… surfer as b…”. This means we can reference the fields in the table surfer by simply using the letter ‘b’. You may use the table name but this can get confusing later on eg. “b.name” == “surfer.name”.
n.b. Its good practice, especially with large queries to use table aliasing. Although MySQL allows you to omit the table/alias name, it will bitch if you reference a field that appears in both tables as it won’t know which one you want.