I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested.
The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant)
CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8
Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:-
SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = '<name obtained from user's click>' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid)
Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper!
I hope I have explained this sufficiently and I TYIA for any guidance
Rich
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Aug 26
Nitin Mehta Re: Having trouble with SQL query
Aug 26, 2012; 22:46
Nitin Mehta
Re: Having trouble with SQL query
Aug 27
rich gray Re: Having trouble with SQL query
Aug 27, 2012; 18:19
rich gray
Re: Having trouble with SQL query
Aug 27
Shawn Green Re: Having trouble with SQL query
Aug 27, 2012; 12:37
Shawn Green
Re: Having trouble with SQL query
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!