SELECT subquery returns NULL values in PostgreSQL

Question

I'm trying to turn this query with INNER JOIN into a nested query.

SELECT starttime, name
FROM cd.facilities
INNER JOIN cd.bookings
USING (facid)
WHERE name LIKE 'Tennis Court%'
AND DATE(starttime) = '2012-09-21'
ORDER BY starttime ASC;

I'm getting somewhere with what I've tried, but apart from the valid values in 'name' I also get a lot of null values. What am I missing? Is this piece of code salvageable?

SELECT starttime, 
(SELECT name FROM cd.facilities
 WHERE name LIKE 'Tennis Court%'
 AND cd.bookings.facid = cd.facilities.facid) AS name
FROM cd.bookings
WHERE DATE(starttime) = '2012-09-21'
ORDER BY starttime ASC;

Answer 1

The inner join returns only rows for where there is a match for facid. The subquery returns null (in name) for where there is no match. If you want to move forward with this, you can probably just eliminate the rows with nulls in name like this for instance:

select * from (SELECT starttime, 
(SELECT name FROM cd.facilities
 WHERE name LIKE 'Tennis Court%'
 AND cd.bookings.facid = cd.facilities.facid) AS name
FROM cd.bookings
WHERE DATE(starttime) = '2012-09-21'
ORDER BY starttime ASC) d where name is not null;

Best regards,Bjarni