CODEWITHSUNDEEP
phpmysqlsqldatabase
Error-404
Error-404

Reputation: 37

Implementing One to One and One to Many Query together in SQL

I have the following tables (Have only included necessary columns) which I'm looking to perform a query on. Some notes:

1) Table 1 has a one-one relationship with Tables 2 & 3, with the common key being menu_id.

Tables 4-6 are a little more complicated:

2) Table 1 has a one to many with table 4, so one menu can have many categories.

3) Table 4 has a one to many with table 5, so one category (Out of multiple above) can have many items.

4) Table 5 has again a one to many with table 6, so one item can have many subitems.

Table 1: -- user_menu --  
         menu_id

Table 2: -- menu_details --
         menu_id

Table 3: -- menu_times --
         menu_id

Table 4: -- menu_categories --
         menu_category_id
         menu_id

Table 5: -- menu_items --
         menu_item_id
         menu_category_id

Table 6: -- menu_subitems--
         menu_subitem_id
         menu_item_id

Question: I need a sql query which will fetch the all associated data from the tables (All columns, I haven't included them here, just the primary key rows) for a specific menu_id on table 1. That seems simple enough, but I'm struggling a little on the one to many part and getting that data in the same query.

I'm expecting something like this. Where menu_id = 2, it will fetch the associated row data (all columns). I've shortened the key names to fit.

user_menu | menu_details | menu_times | menu_categories | menu_items | menu_subitems
-------------------------------------------------------------------------------------
menu_id=2  - menu_id=2  - menu_id=2 -< cat_id=1     -<    item_id=1    subitem_id=1
                                       cat_id=2     -<    item_id=2 -< subitem_id=2
                                       cat_id=3           item_id=3    subitem_id=3
                                                          item_id=4 -< subitem_id=4
                                                          item_id=5 -< subitem_id=5
                                                                       subitem_id=6
                                                                       subitem_id=7

(I've shortened above. The proper names are in the first list, so please use those).

The ID's refer to the primary key rows I need the associated column data for. To clarify again If I were to query for menu_id = 2, I will get the menu_id = 2 row from table 2 & 3. I will also get the categories WHERE menu_id = 2 in the menu_categories table, the item_id rows where menu_category_id = x in the menu_item table, and the subitem rows where item_id = x from the menu_item table, in the subitem table.

I plan on copying the result to an array and passing that back. Just need the query bit (I'm assuming join but I'm not sure which is relevant here).

Thanks!

Upvotes: 1

Views: 82

Answers (1)

Jay Kazama
Jay Kazama

Reputation: 3277

Yes, you can just use join, joining them using the key references should output it like that: 

select A.menu_id, B.menu_id, C.menu_id, D.menu_category_id, E.menu_item_id,  
       F.menu_subitem_id
from user_menu A
join menu_details B on A.menuid = B.menuid
join menu_times C on B.menuid = C.menuid
join menu_categories D on C.menuid = D.menuid
join menu_items E on D.menu_category_id = E.menu_category_id
join menu_subitems F on E.menu_item_id = F.menu_item_id
where A.menu_id = 2

Upvotes: 1

Related Questions