eharetea

Joining tables in a MySQL database.

Tie data from numerous tables together with Joins.

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 01:06 AM

Joining allows you to join tables together.

INNER JOIN:

# basic syntax:

SELECT * FROM table1, table2 WHERE table1.column = table2.column;



# this will list the titles of folders that have a comment in the comment table:

SELECT title FROM folders, comment WHERE folders.folder_id = comment.link_id; 


# using aliases:

SELECT title, link_type FROM comment AS c, folders AS f WHERE c.link_id = f.folder_id;


# joins three tables:

myql>SELECT title, link_type
FROM comment AS c, files, revisions as r
WHERE c.link_id = files.folder_id AND files.folder_id = r.file_id = files.folder_id
ORDER BY c.link_id DESC


LEFT JOIN:

# basic syntax:

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

# column names match, you can use:

SELECT * FROM table1 LEFT JOIN table2 USING (column);


# another example:
$query = 'select a.link_id, f.title, comment.short_desc from comment, folders AS f, associations AS a, categories AS c where (comment.link_id = f.folder_id && f.folder_id = a.link_id && a.category_id = c.category_id && '.$id.' = a.category_id)';