select
concat_ws(' ', firstname, surname) as member,
name,
cost
from
cd.bookings as b
inner join cd.members as m using (memid)
inner join lateral (
select
facid,
name,
case when m.memid = 0 then (f.guestcost * b.slots) else (f.membercost * b.slots) end as cost
from
cd.facilities as f
) as f using (facid)
where
cost > 30
and starttime >= date '2012-09-14'
and starttime < date '2012-09-14' + interval '1 day'
order by
3 desc;
The exercise https://pgexercises.com/questions/joins/tjsub.html can be answered with LATERAL JOIN.
By the way, thank you for the wonderful website.