Difficult multi-directional join
I've since worked out the specific issue this post focused on (the joins)... and have moved on to a new set of issues with it. So to keep the post on topic I'm going to answer/close it up. Nobody disputed the way I ended up joining the tables or suggested a &quot;more correct&quot; or efficient way, so I guess what I have can be considered correct for this situation.
Maybe somebody else who lands on this page will find my visual diagram and subsequent joins helpful in visualizing/joining their own tables... who knows.
Anyways, this is what worked for me:
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id