1

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:

enter image description here

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.

Improve this question
4
  • Tables (bases, views & query results) represent relation(ship)s/associations. Table meanings are necessary & sufficient to query. Constraints--including CKs, PKs & FKs-- are not needed to query. They are consequences of the table relation(ship)/association choices & what situations/states can arise. They are for integrity to be enforced by the DBMS. FKs are sometimes called "relation(ship)s" but they are not. They say that subrows appear elsewhere as a PK/UNIQUE. (But when constraints hold, additional queries return the same results as queries that don't assume constraints.)
    – philipxy
    Oct 7, 2020 at 2:14
  • Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Images cannot be searched for or cut & pasted. Also code questions should have a minimal reproducible example anyway--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. How to Ask
    – philipxy
    Oct 7, 2020 at 3:19
  • Querying isn't based on FKs ("relation(ship)s" [sic]), it's based on table meanings. Relational queries don't have directions. Is there any rule of thumb to construct SQL query from a human-readable description?
    – philipxy
    Oct 7, 2020 at 6:20
  • Please construct a [fiddle]9https://dbfiddle.uk/?rdbms=postgres_12&fiddle=4c205e911cb80df5b56768a1bc8ff6a2) with your table structures - some sample data and what you've tried - without these, we are flying blind! See here.
    – Vérace
    Oct 14, 2020 at 10:57

1 Answer 1

Reset to default
0

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
Improve this answer
5
  • LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, any WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS Why are you even left joining? What use do you think you are making of null-extended unmatched rows? The 1st inner join makes store.id non-null, so the 1st left join could just be inner. ...
    – philipxy
    Oct 7, 2020 at 3:15
  • ... Then via store.id =, menu.store_id is non-null so the 2nd left join could just be inner. Ditto for 3rd & 4th left joins. No nulls kept. So you need no left joins. Another take: With no minimal reproducible example we don't know whether FKs can be null. If they can't be null, all left table rows match & you can't expect nulls from any left join so there's no need to left join. Otherwise, there can be unmatched rows in left joins, but if you want null-extended rows in the result then this query isn't what you want because it is the same as using all inner joins--you have erroneous join order and/or inner vs outer.
    – philipxy
    Oct 7, 2020 at 3:18
  • 2
    @philipxy well it's obvious you have a more complete understanding of all of this than I do, which is why I came here asking for suggestions! So I can't answer your question "What use do you think you are making of null-extended unmatched rows?". It produced the results I needed and I can't tell you exactly why. The way I visualize it is this- so I have my account table, I want all account numbers, then from there all menus for those account nums, then from there all menu_items that match those menus, etc. etc. from there. If you have a better solution, please share!
    – boog
    Oct 9, 2020 at 15:17
  • This question should be closed & not answered until improved per my comments. The comment question by me that you quote is the same as "why did you use left joins". You wrote them, why did you write them, what are the results you wanted & why do you think they produce them--they could just be inner--again, in your question per my comments. Nothing is stopping you from answering to yourself or acting on my comments. Meanwhile I addressed code problems in comments. PS Please clarify via edits, not comments. PS minimal reproducible example
    – philipxy
    Oct 9, 2020 at 22:47
  • "then from there all menus for those account nums, then from there all menu_items that match those menus, etc. etc" You don't explain why you think following a chain of menus should lead to a chain of left joins. Why are you doing it? What are your relevant beliefs? There is no reason that would give you some particular result you wanted. In fact if you clearly wrote out what the resultant rows are for your question's joins & for the rows you want you'd see that they're not the same. That & how to query correctly is discussed in the links of my comment full of links on the question.
    – philipxy
    Oct 14, 2020 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.