Left join select query woe

Hi there,

So I have a one-to-many relationship between authorities and authcourses (courses run by said authorities). One authority can have many courses, but each course belongs to a single authority.

I have a table ‘authorities’ which has the columns: id; name; coord; email; trash.
The ‘authcourses’ table has the columns: id; title; description; closingdate; authorityid and trash.

(Trash is a boolean, when true it is selected by a query to populate the ‘trash can’. This just gives the end user an opportunity to ‘restore’ data if they accidentally delete it.)

OK, so I have a page that I want to display all the authority details, along with all the courses they are currently running, so I need to select all the columns from the authorities and left join all the course details.

Something like this:


select authcourses.id, title, description, closingdate, name, coord, email, authcourses.trash from authorities left join authcourses on authorityid = authorities.id

will bring me the relevant data, but will include any courses that are deemed trash.


select authcourses.id, title, description, closingdate, name, coord, email, authcourses.trash from authorities left join authcourses on authorityid = authorities.id where authcourses.trash < 1;

will miss out authority data for any authority that has courses that have trash set to 1. What I want to do is have the authority data show up with NULL in the relevant course columns. Is this possible, or should I just have the PHP code do something to ignore results where trash is set to 1?

There is a page live here:
Language for Learning

Many thanks,
Mike

SELECT authorities.id
     , authcourses.title
     , authcourses.description
     , authcourses.closingdate
     , authorities.name
     , authorities.coord
     , authorities.email
     , authcourses.trash 
  FROM authorities 
LEFT OUTER
  JOIN authcourses 
    ON authcourses.authorityid = authorities.id 
   [COLOR="Red"]AND [/COLOR]authcourses.trash < 1

Perfect, thanks r937. I’d never come across LEFT OUTER JOIN before.

Mike.