List:General Discussion« Previous MessageNext Message »
From:rich gray Date:August 27 2012 4:19pm
Subject:Re: Having trouble with SQL query
View as plain text  
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...

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

  `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_menuid` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
   PRIMARY KEY (`menuid`)

CREATE TABLE `menu_product` (
  `menuid` int(11) unsigned NOT NULL,
  `productid` int(11) unsigned NOT NULL,
PRIMARY KEY (`menuid`,`productid`),
  KEY `prodidx` (`productid`)

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

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 ( = '<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


Having trouble with SQL queryrich gray26 Aug
  • Re: Having trouble with SQL queryNitin Mehta27 Aug
    • Re: Having trouble with SQL queryrich gray27 Aug
      • Re: Having trouble with SQL queryShawn Green27 Aug