MySQL JOIN

[eluser]IamPrototype[/eluser]
I've read the reference manual on MySQL's website, but I still don't get it. Maybe I'm just really stupid, or it's because I didn't read all of it... Can anybody tell me what JOIN can do while selecting data. This is my view of JOIN so far:

I have a table named users and a table named userprofiles. Now I need to get information about the user and select the profile that belongs to the user.

SELECT * FROM `users` JOIN `usersprofile`

instead of doing two queries like

SELECT * FROM `users`

and then

SELECT * FROM `userprofile` WHERE `uid` = '{$row->$uid}'

...am I completely wrong? And what's up with all the periodes --

Code:

table2.id=table3.id

-- ?

[eluser]jedd[/eluser]
Try something like this instead:

Code:

SELECT
    * FROM users
LEFT JOIN
    userprofiles ON userprofiles.id = users.user_profile_id
WHERE
    users.id = $x

Two notes - first, use underscores between words within table and field names. Second, don't select * from tables unless you really need everything (you rarely do).

[eluser]tekhneek[/eluser]

Code:

SELECT * FROM users AS u, users_profiles AS up WHERE
u.uid = up.fk_uid AND
u.uid = {$row->id}

Something like that maybe? That's essentially the same as

Code:

SELECT * FROM users JOIN users_profiles ON users.uid = users_profiles.fk_uid WHERE users.uid = {$row->uid}

Or that

[eluser]IamPrototype[/eluser]
What I wanted to know is what JOIN does.... is it just getting two tables in one query that writing two quieries? How would my output syntax look like if I need to display info from users and usersprofile.

And fk_uid is the id row in userprofiles table right?

[eluser]tekhneek[/eluser]
Essentially you're joining hence "JOIN" the two tables together using a unique identifier (in most cases) such as an ID or UID, either or -- the concepts are the same whereas the naming is unique.

In database normalization 1st through 3rd normal forms the primary concerns are assuring that each table only contains the necessary information to describe itself entirely. You wouldn't have a "comments" column inside of the photos table, rather you'd have "photos_comments" where you used the photos ID as a foreign key on the photos_comments table

Code:

photos.id = photos_comments.photos_id WHERE photos.id = $row->id

You'll notice that the photos.id = photos_comments_photos_id is obviously signifying a relationship between the two, however this type of "JOIN" sometimes referred to as an "EQUIJOIN" or just plain "JOIN" means that we're going to get all of the comments for their corresponding photo.

The PROBLEM with this is that if you have 10 comments for 1 photo you're going to return the photos table, as well as the photos_comments table 10 times. You'll have 10 unique rows from the comments table and 1 identical photos row repeated because you never specified a unique identifier such as a photo id (or photos.id)

I noticed that you have userprofiles and users -- can a user have more than one profile? If not then this is considered a "one-to-one" relationship meaning that the only purpose of joining these is to make the resulting query easier to use. You might be able to combine userprofiles into the users table depending on the unique needs of your database architecture/application workflow.

In plain english "Let's get all of the users and their corresponding profiles for a given user id, and the users id that we want this information for is {$row->id}" albeit the majority of the time you will be using a variable substitution over a static user id (because if you got the user with id 5 every time this is a very linear approach, and not dynamic.)

I hope I explained this for you better -- I'm not an SQL expert so here are some resources that have helped me grasp some of these concepts.

http://www.codinghorror.com/blog/archives/000976.html <-- Visual explanation of JOINs (VERY good read.)
http://en.wikipedia.org/wiki/Database_normalization <-- Database normalization

Hope this helped.

[eluser]IamPrototype[/eluser]
Thanks a lot - it helped me a lot and I'll continue reading on your links ASAP. It is a "one-to-one" relationship, by the way.

[eluser]tekhneek[/eluser]
Cool. Glad I could help.

[eluser]IamPrototype[/eluser]
If I need to display data from users and userprofiles it works like a normal query, right?

Code:

// Mysql JOIN Query
// ...
foreach($query as $row)
{
echo $row->uid . '<br />; // from users
echo $row->website; // from userprofiles
}