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:
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?
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.