MySQL Join Tutorial
Tabel cars
- ID
- name
Tabel colors
- ID
- color
- car_ID
In the first table are all the cars. In the second table there are all posible colors for the cars. Since a car can have more colors we'll make a join to see wich colors are available for a car.
First we are going to fetch all cars:
MYSQL
- SELECT
- *
- FROM
- cars
As you can see, i don't write the query in one line as you might be used, but over several lines. This is a bit useless in simple queries as this one, but if you have got some complex queries, this can improve the readability of your query. Get used to doing this when you are writing complex queries to keep things simple for yourself (and other coders).
MYSQL
- SELECT
- *
- FROM
- cars
- JOIN
- colors
- ON
- colors.car_ID=cars.id
This query looks a lot like the first one, but we added a few lines. At first, after the JOIN, we state wich table we want to join with. In this case the "colors" table. Then is the difficult peace... After the ON, we state on wich columns the tables should join. Every color has a reference to a car by the "car_ID" column. Every car has an ID, and these two columns are the link between the two tables. These are the columns we want to join.
In complex queries, always point to the table you're talking about. For example; if you use "WHERE id=3", MySQL doesn't know wich ID you're talking about. Both tables have a column "ID", so you have to say to MySQL wich table you mean. To do this, put the name of the table in front of the column name.
In case of table names that are very long, you can abbreviate there names to keep things simple and clean. This is done like so:
MYSQL
- SELECT
- *
- FROM
- cars AS cr
- JOIN
- colors AS cl
- ON
- cl.car_ID=cr.id
As you can see in above query, i state that MySQL should name the table cars "cr" from now one, and the table colors should be names "cl". This can save you many typing and keeps things nice and clear.
Comments
Name: strony internetowe tworzenie (27 December 2008 - 15:52)
This looks good! Really good tutorial include so many helpful informations!Excellent SITE. I will refer people to your ITEMS. Effective use of Wordpress had some exceptional.
Cheers
Name: rental epc (22 December 2008 - 22:46)
This looks good! Really good tutorial include so many helpful informations!
Excellent SITE. I will refer people to your ITEMS. Effective use of Wordpress had some exceptional.
Cheers
Name: Peter Joseph (13 March 2008 - 08:09)
Actually these all about to get record from table . But wat if we wanna have to insert record in two table . having one to one relation ship.Name: DreamDealer (22 January 2008 - 14:34)
@Deepesh:No matter how many tables you want to join, the one thing they all need to have in common is one column that connects one table to another. For example, if you would have a thirth table in the example above, something like engine types, you would want to join it with the cars table and use the car_id as an identifier for the query. Every color is connected to a car by the column car_id, and then every engine will be connected to a car by a column car_id.
Name: Deepesh (14 January 2008 - 09:33)
I want to know what is the fundamental things to join more than two tables.Please tell me soon . Thanks!!!!!!!!!!!!!!!
