To understand the complete journey of a menu item and its associations:

Start with Menu Item:

• Table: gdata_menu_item
• Key Attributes:
  • id - Unique identifier for the menu item.
  • categoryid - Determines which category the item belongs to.
  • recipeid - Links to the recipe for the menu item.
  • brandid - Indicates which brand offers this menu item.
  • menucategoryuniqueid - Associates with the specific category under a menu.

Category Information:

• Table: gdata_menu_category
• Key Attributes:
  • id - Unique identifier for the category.
  • menuid - Connects to the overarching menu this category is a part of.

Overall Menu Information:

• Table: gdata_menu
• Key Attributes:
  • id - Identifier for the menu. This will tie back to the menu category and further to the menu item.

Recipe Details:

• Assuming there's a table, say gdata_recipe, the recipeid from gdata_menu_item would lead you here.

Brand Details:

• Depending on how brands are stored, you'd use the brandid from gdata_menu_item to fetch brand details.

Locations Offering the Menu Item:

• Table: gdata_menu_location
• Key Attributes:
  • locationid - Specifies where this menu is available.
  • menuid - Links back to the menu in which the menu item exists.

Pricing of the Menu Item:

• Table: gdata_menu_item_price
• Key Attributes:
  • menuitemid - Connects to the specific item for which the price is set.
  • price - The actual numerical value representing the cost.

Example

To retrieve details for a specific menu item:

• Start at gdata_menu_item using the id.
• Fetch the category details from gdata_menu_category using menucategoryuniqueid.
• Use the menuid from the category table to get overall menu details from gdata_menu.
• Navigate to the gdata_recipe table using recipeid to understand the item's composition.
• Identify the brand with the brandid.
• Find the locations serving this item by matching menuid in gdata_menu_location.
• Get the pricing for this item from gdata_menu_item_price using menuitemid.

By following this path, you get a comprehensive view of the menu item, its recipe, price, where it's served, under which category, and the menu it's part of.

SQL Query:

select
      mi.id as menu_item_id,
      b.name as brand_name,
      loc."name" as location_name,
      m.name as menu_name,
      mc.name as category_name,
      r.name as menu_item_name,
      r.description as recipe_description,
      rmg."name" as modifier_group_name,
      rm."name" as modifier_name,
      mip.price as item_price
from
     public.gdata_menu_item mi
left join public.gdata_menu_category mc on
     mi.menucategoryuniqueid = mc.id
left join public.gdata_menu m on
     mc.menuid = m.id
left join public.gdata_recipe r on
     mi.recipeid = r.id
left join public.gdata_menu_item_price mip on
     mi.menuitemid = mip.menuitemid
left join public.gdata_brand b on
     mi.brandid = b.id
left join public.gdata_menu_location ml on
     m.id = ml.menuid
left join public.gdata_location loc on
     ml.locationid = loc.id
left join public.gdata_recipe_modifier_group rmg on
     r.id = rmg.recipeid
left join public.gdata_recipe_modifier rm on
     rmg.id = rm.modifiergroupuniqueid
where
     mi.id = 'YOUR_MENU_ITEM_ID';