title: select query for category data with parent child relationship
body: <p>I have this db table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `parent_id` int(11) NOT NULL,
    `name` varchar(100) NOT NULL,
    `inherit` enum('Y','N') NOT NULL DEFAULT 'N',
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
  
  --
  -- Dumping data for table `category`
  --
  
  INSERT INTO `category` (`id`, `parent_id`, `name`, `inherit`) VALUES
  (1, 0, 'Fruits', 'N'),
  (2, 0, 'Electronics', 'N'),
  (3, 0, 'Furniture', 'N'),
  (4, 0, 'Garden', 'N'),
  (5, 1, 'Apples', 'N'),
  (6, 1, 'Bananas', 'N'),
  (7, 5, 'Green Apples', 'Y'),
  (8, 5, 'Red Apples', 'N'),
  (9, 2, 'Mobiles', 'Y'),
  (10, 2, 'Televisions', 'N'),
  (11, 9, 'Android', 'N'),
  (12, 9, 'iPhone', 'Y'),
  (13, 7, 'One Dozen Green Apples', 'Y'),
  (14, 7, 'Two Dozens Green Apples', 'N');
  </code></pre>
  
  <p>There is another table where I keep user_id, category_id e.g. 
  user_id 1000 can see 1 and 5, I put this info in the sessions so that my query becomes </p>
  
  <pre><code>SELECT *
  FROM `category`
  WHERE id
  IN ( 1, 5 )
  </code></pre>
  
  <p>this query shows <code>Fruits &gt; Apples</code> - all this works fine. But, I have marked "<code>Green Apples</code>" as <code>Inherit = 'Yes'</code> so user 1000 should see "<code>Green Apples</code>" too but not "<code>Red Apples</code>". If there are sub-categories under Green Apples marked as inherit = 'Y'... e.g. "One Dozen Green Apples" should be listed too!!</p>
  
  <p>I thought to give UNION a go but can't figure out how to get more than 2 levels deeper ...</p>
  
  <pre><code>SELECT * FROM (
      SELECT *
      FROM `category`
      WHERE id
      IN ( 1, 5 )
  
      UNION
  
      SELECT c.*
      FROM `category` c
      INNER JOIN `category` parent ON parent.id = c.id AND c.inherit = 'Y'
      WHERE c.parent_id
      IN ( 1, 5 )
  ) all_cats
  </code></pre>
  
  <p>What would you suggest to me? I am open table structure changes if that'd make querying any easier? thanks</p>