Difficult multi-directional join

All we need is an easy explanation of the problem, so here it is.

I’m being asked to create what (at first) seemed to be a fairly simple/straight forward query. However, I am running into some problems caused by the link relationships between the tables being queried. And the wide range of data to be included on the report from various different tables.

This is a postgresql database that contains information on cooks who sell food items. Simple enough. The requested report queries for personal information on the cooks, their available menus, food items on those menus, and the last time that item was purchased…data which resides on 6-7 different tables.

I’m having a really difficult time JOINing all of these tables together in a way that returns the data in an acceptable way. Here is a visualization of the tables I’m working with and their link relationships:

Difficult multi-directional join

As you can see, everything originates and links back to the user’s entry on the account table. From there, sellers have an entry on the "store" table, which links to pretty much everything else:

  • ‘store_address’ and ‘address’ tables contain address info.
  • ‘menu’ table contains the names of the menus- each store can have multiple menus which offer certain food items.
  • kitchen_item table contains all of the specific food items available on each of the various menus.
  • ‘menu_item’ table is what’s used to link the food items on kitchen_item table to it’s menu on the menu table.
  • ‘orders’ tracks order information for each item sold

So I’m having trouble joining all of these tables together correctly. I need to be able to show each seller (account table), their personal info (address table), their available menus (menu table), the items on each of those menus (kitchen_items table), and the date of that item’s last sale (orders table).

When I simply left join everything, every possible menu contains every possible food item… I attempted a combination of left joins, right joins, full joins, etc… but I think this one might be a little above my skill level… given the layout of the relationships.

So, would anybody be so kind as to demonstrate these tables might be joined together?

the joins:

select 
account.id, account.email, account.firstname, account.lastname, 
address.address, address.address_2, address.city, address.state, 
menu.name, 
kitchen_item.name, 
orders.placed
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)
left join orders on store.id = orders.store_id
join store_address on store.id = store.address.store_id
join address on store_address.address_id = address.id
group by table.value, table.value...

When I run the query with the tables joined like above, it’s just missing random entries… and I’m not sure why.

The data is for a report. Tabular "everything together" or split into subject areas is fine – though split into subject areas might look nice. The only requirement is a column for each data point.

The last part I’m trying to accomplish is to join the table "orders" to obtain the newest single entry in orders.placed for each already existing row/food item. I’d imagine I need to write a subquery?

I completely agree with splitting the query up, though this is strictly the query that the individual wants. I would guess they’re going to want me to implement some sort of " case when rowcount() >1 = ” " to clear out all of the duplicate entries and turn it into whitespace.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 "more correct" 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply