CODEWITHSUNDEEP
mysqljoin
turbonerd
turbonerd

Reputation: 1306

MySQL joins: displaying all rows in a table whilst using "where" clause

I have the following tables:

slots = id, time

child = id, parent_id, name, form

parent = id, name, contact_details

teacher = id, name, subject

appointments = id, child_id, slot

I'm using the following code to display a list of bookable appointment times:

SELECT s.id, DATE_FORMAT( s.time,  '%l:%i' ) AS TIME, p.name
FROM slots AS s
LEFT JOIN appointments AS a ON a.slot = s.id
LEFT JOIN child AS c ON a.child_id = c.id
LEFT JOIN parent AS p ON c.parent_id = p.id

This displays a list of all of my appointment slots and a few with parent names.

What I'd like to do is, for a specific teacher, display a list of all appointment slots, and if some have been filled, I'd like to show the name of the parent of the child who has booked that slot.

For example,

Miss Broomfield's Appointments:

id  TIME    name
1   4:00    Parent One
2   4:05    NULL
3   4:10    Parent Two
4   4:15    NULL
5   4:20    NULL
6   4:25    NULL
7   4:30    NULL

Is this possible? I've tried using a WHERE clause but that simply displays the booked appointments rather than the full list of times available.

I thus realised that I'm fairly sure I've got my JOINs entirely wrong!

UPDATE: slots table looks like this:

SQL result

Host: localhost
Generation Time: Mar 05, 2012 at 11:43 AM
Generated by: phpMyAdmin 3.4.9 / MySQL 5.5.20
SQL query: SELECT * FROM `slots` LIMIT 0, 30 ; 
Rows: 30

id  time
1   2012-03-15 16:00:00
2   2012-03-15 16:05:00
3   2012-03-15 16:10:00
4   2012-03-15 16:15:00
5   2012-03-15 16:20:00
6   2012-03-15 16:25:00
7   2012-03-15 16:30:00
8   2012-03-15 16:35:00
9   2012-03-15 16:40:00
10  2012-03-15 16:45:00
11  2012-03-15 16:50:00
12  2012-03-15 16:55:00
13  2012-03-15 17:00:00
14  2012-03-15 17:05:00
15  2012-03-15 17:10:00
16  2012-03-15 17:15:00
17  2012-03-15 17:20:00
18  2012-03-15 17:25:00
19  2012-03-15 17:50:00
20  2012-03-15 17:55:00

Thanks in advance,

Upvotes: 1

Views: 294

Answers (2)

Mister Bee
Mister Bee

Reputation: 79

OR you could put the following in the WHERE

WHERE appointments.teacher_id =$teacher OR appointments.teacher_id IS NULL

Upvotes: 1

turbonerd
turbonerd

Reputation: 1306

An answer from a friend in IRC seems to have done the trick:

SELECT s.id, DATE_FORMAT( s.time,  '%l:%i' ) AS time, p.name
FROM slots AS s
LEFT JOIN appointments AS a ON a.slot = s.id
AND teacher_id =$teacher
LEFT JOIN child AS c ON a.child_id = c.id
LEFT JOIN parent AS p ON c.parent_id = p.id

The AND line is the important one!

Upvotes: 1

Related Questions