Joining three three tables

Question

I have this diagram that should explain my situation. I need help joining three tables, which I have no idea how to do this:

Therefore, I can go through the while loop to get the records by doing the following:

<img src="<?php echo $row['filename']; ?>" alt="" /> Album: <?php echo $row['album_name']; ?> AlbumID: <?php echo $row['album_id']; ?> 

Answer 1

Using INNER JOIN will not allow you to return albums that do not have images. ORDER BY ... DESC sorts the results in descending order, but I'm not sure how to return only the last record. It will take some combination of ORDER BY, GROUP BY and TOP , maybe.

 SELECT album_table.album_id, album_table.album_name, images_table.filename FROM album_table INNER JOIN images_table ON images_table.album_id = album_table.album_id WHERE album_table.user_id = uid ORDER BY images_table.date DESC 

Answer 2

I believe that INNER JOIN is what you are looking for

This site gives a good example.

http://www.w3schools.com/sql/sql_join_inner.asp

Answer 3

@lolwut: Try it -

 SELECT album_table.album_id, album_table.album_name, images_table.filename FROM album_table INNER JOIN images_table ON images_table.album_id = album_table.album_id INNER JOIN users_table ON users_table.uid = album_table.user_id WHERE (users_table.uid = ' . $uid . ') ORDER BY images_table.date DESC 

$uid should be the current login session ... or you can leave this WHERE completely if you want to return information from all users.

Answer 4

In fact, in this case you only need to join the two tables (as a general rule, the less joins you make, the better).

Since you know the user ID, you can request it directly from the album table name.

If another column "username" appears in your users_table account and you want to find it, you will need to join the user table.

 SELECT filename, album_name, album_id FROM album_table at, images_table it AND at.album_id = it.album_id AND at.user_id = $user_id