You can just run two queries to get data from two tables but it is more efficient to do it in one. JOIN is used to pick the tables and ON says how to make the join:
$query = "SELECT firsttable.cdTitle, secondtable.trackTitle FROM firstable JOIN
secondtable ON firsttable.cdReference = secondtable.cdReference";
Some of that is not strictly necessary but it does avoid problems later on. The dot operator (the full stop/period) is used to separate the table and the field in that table. This allows you to get data from two fields with the same name (one in each table).
This query gets data from two fields from the tables. The tables are JOINed where the fields cdReference match.
Often the data will be restricted with a WHERE:
$query = "SELECT firsttable.cdTitle, secondtable.trackTitle FROM firstable JOIN
secondtable ON firsttable.cdReference = secondtable.cdReference WHERE firsttable.cdReference=1";
This will list only one record from firsttable (the title of the CD with reference 1). It will then JOIN all the data from secondtable where the cdReference matches. The result should be a list of tracks on that CD.
Try these queries using the tables and data you created previously (here and here) then get the data with mysql_fetch_array() and display it with print_r() inside a WHILE loop to see what the data looks like. If you don't have the tables you can import them here and here using PHPMyAdmin's import tab.
Now that you know how to get and display data well you should practice as much as possible.Exercise on getting and displaying data
Outer joins
A simple JOIN links data from two tables where a field matches and is properly known as an INNER JOIN. That means that if there is no matching data in the child table then some of the data from the parent will not be shown. If there is no matching data in the parent table then some of the data in the child will not show. Imagine a database of sales people and sales they have made. IF one sales person has no sales they disappear from the list of sales people and sales. OUTER JOIN avoids this problem by showing data from the other table even when there is no matching data in the other. The syntax is almost the same as a simple join but now CDs with no tracks (or where tracks haven't been entered yet) will appear:
$query = "SELECT firsttable.cdTitle, secondtable.trackTitle FROM firstable LEFT OUTER JOIN
secondtable ON firsttable.cdReference = secondtable.cdReference";
The word LEFT specifies that it is the parent table (the "one" side of the relationship) from which all records must be shown. Try adding a CD with no tracks to see the results.
A RIGHT OUTER JOIN would include tracks with no CDs. This would not be needed as often because in a properly designed relational database there should not be child records with no parents.
The use of LEFT and RIGHT comes from entity relationship diagrams which represent the one-to-many relationships in relational databases and traditionally the "one" side is on the left:
If there is any chance that records in the parent table might not have matching data in the child table you should use OUTER joins to make sure you see the full data.
You might see an alternative syntax in MySQL which is to drop the OUTER word and just use LEFT JOIN and RIGHT JOIN. This probably makes it easier to read the query but it is also less compatible with other SQL servers.
