From: rich gray
Date: August 27 2012 4:19pm
Subject: Re: Having trouble with SQL query
List-Archive: http://lists.mysql.com/mysql/228089
Message-Id: <503B9E06.3010704@richgray.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Nitin
Thanks - I tried that and got 0 rows...
I have spent more time on describing my problem -- see below hopefully 
this will make the issue more clear...
Rich
<<<<<<<<<<<<<<<<<

I have a MySQL database with a menu table and a product table.

- The products are linked to the menus in a one-to-many relationship 
i.e. each product can be linked to more than one menu
- The menus are nested in a parent child relationship
- Some menus may contain no products

The desire is that when a user clicks on a menu entry then all products 
linked to that menu - there may be none - will get displayed as well as 
all products linked to any child menus of the menu clicked on ...

So say we have a menu like this:-

Motor cycles -> Sports bikes -> Italian -> Ducati
Motor cycles -> Sports bikes -> Italian -> Moto Guzzi
Motor cycles -> Sports bikes -> British -> Triumph
Motor cycles -> Tourers -> British -> Triumph
Motor cycles -> Tourers -> American -> Harley-Davidson
.
etc etc

Clicking on 'Sports bikes' will show all products linked to 'Sports 
bikes' itself as well as all products linked to ALL menus below 'Sports 
bikes', clicking on 'Harley-Davidson' will just show products for that 
entry only.

Below are 'describe table' for the 2 main tables in question NB there is 
a 3rd table that holds product descriptions which I won't show as I 
don't think it is relevant here:-

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 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

For the sake of this question I will simplify it and say there is only 2 
levels of nesting i.e. root level and 1 level below that... 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 when I run the above query it returns far too 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