Link to home
Create AccountLog in
Avatar of JCWEBHOST
JCWEBHOST

asked on

sql menu with products

hey guys i have  two tables menu and products

menu table

id
parent_id
title

and products

id
menu_id
p_name

my menu i build has sub categories

now i need to select all products and list the sub categories of the products
Avatar of Bardobrave
Bardobrave
Flag of Spain image

This query should return you all your products with it's associated menu title. If a product hasn't an associated menu it won't be showed by the query, if you want those elements to show just change the INNER JOIN to a LEFT JOIN

SELECT P.p_name, M.title FROM products P INNER JOIN menu M
ON P.menu_id = M.id
Avatar of JCWEBHOST
JCWEBHOST

ASKER

and if the product have more than one sub category?

i need to pull all the categoery linking to the product
SELECT MENU_ID,PARENT_ID,TITLE,P_NAME
 FROM TBLPRODUCTS P
INNER JOIN TBLMENU M ON P.MENU_ID = M.ID
How are categories related to products?
i aslo want to list all sub category of the product.
i have two tables menu and products

menu have
id       parent_id   title

and products
id  menu_id     p_name

now i build a menu like

id    parent_id    title
1         null           products
2           1                 sub
3            2                sub 2
4             3                sub 3


in my products table  

id       menu_id     p_name
1             4               product
So... if I'm understanding you well, you want to show the menu related to a product and all the submenus whose parent is the menu associated to the product.

To do that you'll need two queries. One to get each product and his menu associated and other to get each submenu descendant from the product's menu.

So you use the first query we'd suggested you and loop over it, on each iteration you'll need to execute a query like:

SELECT title FROM menu WHERE parent_id = <menu_id of the current product on the loop>

Which will give you the subcategories of the product.
please show me i one full query please
First  you execute this query:

SELECT P.p_name, M.title FROM products P INNER JOIN menu M
ON P.menu_id = M.id

Then you loop through their results and execute the other query

foreach result on queryResults
    SELECT title FROM menu WHERE parent_id = result.menu_id


You cannot obtain those data on a single query, as the parent_id is extracted from the table during query execution, and so you cannot use that value to extract the subcategories during the same query.
i am lost, do i store the the first query in a data table and then excute the secound query on the table?
ASKER CERTIFIED SOLUTION
Avatar of Bardobrave
Bardobrave
Flag of Spain image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account