Tables joining other Tables?

Orc · 02-09-2008, 05:52 PM

How would I join tables with other tables, say if I wanted to create something needed another table, how would I bring that tables columns, rows, values into the current table?

Alan @ CIT · 02-09-2008, 06:41 PM

Joins can be a huge pain sometimes when you are trying to get the correct data from multiple tables

Have a read of this tutorial which should get you started - it gives a good introduction to selecting / updating data across multiple tables in MySQL.

Alan

Orc · 02-09-2008, 06:43 PM

Quote:

Originally Posted by Alan @ CIT

Joins can be a huge pain sometimes when you are trying to get the correct data from multiple tables

Have a read of this tutorial which should get you started - it gives a good introduction to selecting / updating data across multiple tables in MySQL.

Alan

So shared columns can only work when joining stuff? Cause that's what I sorta had in mind.

Alan @ CIT · 02-09-2008, 07:23 PM

I'm not sure what you mean by shared columns.

The idea behind using joins is to split data across multiple tables. For example, say you have a users table:

Code:

userid | username | password
----------------------------
1      | alan     | secret
2      | orc      | something

And you decide to store the users email addresses in a seperate 'emails' table:

Code:

emailid | address | userid
--------------------------
...more rows here...
15       | alan@example.com | 1
16       | orc@example.com  | 2

You can see that the 'userid' column in our 'users' table, matches the 'userid' column in our 'emails' table. If we wanted to fetch a username and their email address, we would do a join across the two tables using the 'userid' column to get the data we wanted.

Is this what you meant by shared columns?

Alan

xenon · 02-09-2008, 07:59 PM

Bear these advices with you:

- you want to get data from different tables, but you need each table to return some data -> use an inner join
- you want to get information that's required to be only in the leftmost table -> use a left join
- you want to get information that's required to be only in the rightmost table -> use a right join.

Perhaps these will create some confusion, that's why I need to come up with some examples. Let's start with 3 very simple tables: users, emails and phones. The structures are, as follows:

Code:

users:
- id (int 8 auto increment)
- name (varchar 64)

Code:

emails:
- id (int 8 auto increment)
- email (varchar 128)
- user_id (int 8, index)

Code:

phones:
- id (int 8 auto increment)
- phone (varchar 128)
- user_id (int 8, index)

Now, the sample data.

Code:

users:
1, user1
2, user2
3, user3

Code:

emails:
1, user1@website.com, 1
2, user2@website.com, 2

Code:

phones:
 1, user1.111.222, 1
 2, user3.001.002, 3

As you can see, user1 has all data filled in. However, user2 has only an e-mail and user3 has only a phone number.

INNER JOIN (this will fetch data for a user that has information in all tables used in the join):

Code:

SELECT u.name, e.email, p.phone
FROM users u
INNER JOIN emails e ON(u.id=e.user_id)
INNER JOIN phones p ON(u.id=p.user_id)

will return:

Code:

name: user1,
email: user1@website.com,
phone: user1.111.222

If we remove the e-mail for the user1, then we will not have any rows returned, unless we remove the email field from the join, or we change the join type.

LEFT JOIN (this will fetch data from all tables, but only the first table used in the join must contain some rows):

Code:

SELECT u.name, e.email, p.phone
FROM users u
LEFT  JOIN emails e ON ( u.id = e.user_id ) 
LEFT  JOIN phones p ON ( u.id = p.user_id )

This query will return all the 3 users, but the phone field will be empty for the user2 (NULL), and the e-mail field for the 3rd will be NULL, aswell. However, the rows ARE returned, because all 3 users exist in the leftmost table used in the join.

The RIGHT join is the same with the left, only switch the leftmost with the rightmost table. If you run it, the 1st and 3rd users will be returned.

Orc · 02-09-2008, 08:04 PM

what does it mean for a leftmost table and rightmost table?

xenon · 02-09-2008, 09:42 PM

leftmost - the table you're joining first
rightmost - the table you're joining last

If you look at a query, you'll see that I referred to them by the position in the query.

Code:

SELECT m.*, n.*, k.* FROM table1 m LEFT JOIN table2 n LEFT JOIN table3 k