You might be tempted to do this, but it probably won't work
Code:
select *
from men m
join women_slept_with w using (man_id)
left join car_owned co using (man_id)
where w.slept_rank=1 and co.owned_rank=1
Because the co.owned_rank is outside of the left join, so it will filter rows *after* the join, and exclude anyone who doesn't have a car. You could probably do it like this
Code:
select *
from men m
join women_slept_with w using (man_id)
left join car_owned co using (man_id)
where w.slept_rank=1 and (co.owned_rank=1 or co.owned_rank is null)
Because when the dude doesn't have a car, all the fields from co will be null.
You could also put the logic into the join filter
Code:
select *
from men m
join women_slept_with w using (man_id)
left join car_owned co on (m.man_id=co.man_id and co.owned_rank=1)
where w.slept_rank=1
This works because the left join guarantees at least one row, regardless of whether the filter criteria is met.
There are *lots* of other ways you could do it to, and depending on your database type and your indexes some might work better than others.
(Note that this filters out the virgins because they won't have any records in women_slept_with at all, much less any with rank=1 and we aren't using a left join on that table)