Retrieving Records From 3 Tables

[peppericious]

I have teachers and colleagues registering for a conference. Teachers can register for themselves only, or they can register themselves and colleagues. The schools they work in is a separate table.

 

I want to pull data from the teachers, colleagues and schools tables so that conference list looks something like this:

 

Joe Bloggs, AAA School

Mary Bloggs, BBB School

Jimi Hendrix, BBB School

<<< colleague of Mary Bloggs

BB King, BBB School

<<< colleague of Mary Bloggs

Jane Doe, CCC School

William Trevor, DDD School

Bart Simpson, EEE School

 

Homer Simpson, EEE School

<<< colleague of Bart Simpson

Marge Simpson, EEE School

<<< colleague of Bart Simpson

John Walton, FFF School

 

I can't figure out how to build my query to pull the data from the tables such that the people are listed as above.

Colleagues are tied to the main teacher (in blue above) by teacher_id. Teachers are tied to schools via school_id.

 

My (trimmed-down) tables are:

teachers

teacher_id, first_name, last_name, school_id

colleagues

id, teacher_id, first_name, last_name

school

school_id school_name

 

 

I tried this, but it's not doing what I want... (

 

$q = "SELECT

t.first_name,

t.last_name,

c.first_name AS cfirst,

c.last_name AS clast,

s.sch_name

FROM

teachers t,

colleagues c,

schools s

WHERE

t.teacher_id = c.teacher_id

AND

t.school_id = s.school_id

";

 

The problem with the query above is that it's only pulling out teachers with colleagues. But I also need teachers who don't have colleagues to be retrieved.

 

Any help much appreciated. Thanks in advance.

[mikosiko]

.......

 

The problem with the query above is that it's only pulling out teachers with colleagues. But I also need teachers who don't have colleagues to be retrieved.

 

 

re-write your query to use explicit JOINS notation (you are using implicit notation for its), and a LEFT JOIN should solve your issue.

http://dev.mysql.com/doc/refman/5.5/en/join.html

[Barand]

this post http://forums.phpfreaks.com/topic/268903-tree-menu-from-database-3-levels-2-records-on-first-level/?do=findComment&comment=1381731 uses categories/subcategories instead of teachers/colleagues but the principle of creating the tree structure is the same and should be easy to adapt.