What this says is the grab the bac (Business Associate Code) from the cars table if the newest corresponding record in the transmission_log table has a status of 'Pending'.
SELECT c.bac FROM transmission_log tl0, cars c
WHERE (tl0.creation_date, tl0.cars_id) IN ( SELECT MAX(tl1.creation_date), tl1.cars_id FROM transmission_log tl1 GROUP BY tl1.cars_id )
AND tl0.status = 'Pending' AND c.cars_id = tl0.cars_id
And to think that when I started here, I was still getting comfortable with two/three table joins. In the great scheme of things, this probably isn't complex SQL but it's pretty complex for me.