Return all rows from one table, and match with a subset of rows from another table?

Question

I have the following two tables:

rsrpID  rsrpName
1       Library Catalog
2       Interlibrary Loan
3       Academic Search Complete
4       JSTOR
5       Project Muse
6       LibGuides
7       Web Resource
8       Other (please add to Notes)
9       Credo Reference

rsriID  rsrirsrpID  rsrisesdID
603     6           243
604     1           243
605     7           243
606     8           244
607     6           245
608     8           245

What I'm trying to do is return the whole first table, and, for those rows in the second table that match the rsrpID in the first table, return those on the relevant rows alongside the first table, for example:

rsrpID  rsrpName                    rsrisesdID
1       Library Catalog             243
2       Interlibrary Loan           
3       Academic Search Complete    
4       JSTOR                       
5       Project Muse                
6       LibGuides                   243
7       Web Resource                243
8       Other (please add to Notes) 
9       Credo Reference             

...but I can't for the life of me figure out a join statement that'll return this. Currently the query I was given is

select rp.rsrpID as ID, rp.rsrpName as Name,
    (select if((count(rsrisesdID) > 0), 'checked', '') 
         from resourcesintroduced ri 
        where (ri.rsrirsrpID = rp.rsrpID) 
          and (rsrisesdID = 243) ) as 'checked' 
  from resourcesintroduced ri,
     resourcepool rp 
 where rsrisesdID = 243 
 group by ID 
 order by Name asc;

As you can see that query is clunky and, if a particular rsrisesdID doesn't appear at all, then the query returns no rows at all.

Answer 1

You are looking for an Outer Join:

select rp.rsrpID as ID, rp.rsrpName as Name, ri.rsrisesdID
  from resourcepool rp 
  left outer join resourcesintroduced ri on (ri.rsrirsrpID = rp.rsrpID and ri.rsrisesdID = 243)