From: rich gray
Date: August 26 2012 9:16pm
Subject: Having trouble with SQL query
List-Archive: http://lists.mysql.com/mysql/228086
Message-Id: <503A9237.2030907@richgray.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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