joining stuff back together

Being selective

Now that we've split up our data into student, course and subject tables we can start looking at how to extract information from them by putting them all back together again :-P

We do this with our tried and true select statement - listing the fields (now for each table we're interested in), the tables, and how they are related to each other.

For example, to list all the students and which courses they are enrolled in we would enter:

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID

Try to list all the courses and their subjects with a similar query.

Really being selective

To restrict the results to just the student called 'John' we add to the where clause using AND...

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID
and name = 'John'

Try this with your earlier subjects query, restricting it to just show the subjects for 'Cert IV General'

Restricting rows on a key field

We can also restrict rows using numeric values (for a primary or secondary key, for example). So we could find all students studying the Cert IV web course using this query:

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID
and courses.courseID = 2

note: since there is a courseID in both the students and the courses table we need to tell the database engine which one we're using in the query. We do this by adding the table name to it: courses.courseID.

Try building a query that lists all the subjects for courseID = 1 (the cert IV general)

Pulling out all stops

Have a crack at listing all the courses, subjects and students from all three tables. (Hint: you will need to join the subjects and students tables and the courses and students tables). See the comments on this post for the answer.

Exercise your new-found knowledge by reading through and trying the examples in our course notes on joins (pages 11 to 14).

There's another way of connecting tables, using the Select .... from ... join ... using ... syntax, which is explained in these exercises on sqlzoo about joining tables, and this slightly more advanced joining tables lesson both are highly recommended.

Next week we take a little break from learning new SQL statements.

We're going to learn some new PHP statements instead :-)

see you then...

Image: 'cadillac ranch'
www.flickr.com/photos/42737578@N00/23249259