I'm more of an hit and try guy and do good only with a data set available.
Still I think making a little change might do the trick.
SELECT DISTINCT
p.productid,
pd.name
FROM menu_product as p
INNER JOIN menu as m ON (m.menuid = p.menuid AND m.name = 'name obtained from
user's click')
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 p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE
p2.menuid = m2.menuid)
Hope that works.
Regards,
From: rich gray r...@richgray.com
To: mysql@lists.mysql.com
Sent: Monday, August 27, 2012 2:46 AM
Subject: Having trouble with SQL query
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
CREATE TABLE `menu_product` (
`menuid` int(11) unsigned NOT NULL,
`productid` int(11) unsigned NOT NULL,
PRIMARY KEY (`menuid`,`productid`),
KEY `prodidx` (`productid`)
) ENGINE=MyISAM 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
