Self join with where clauslue problem

Databases
1

Hi

This is my table:


--
-- Table structure for table `faq_categories`
--

CREATE TABLE IF NOT EXISTS `faq_categories` (
  `catid` int(11) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(37) NOT NULL,
  `parentid` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `metatags` text NOT NULL,
  `sorder` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL,
  `categoryphoto` varchar(255) NOT NULL,
  PRIMARY KEY (`catid`),
  KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=76 ;

I need to query the database so I can find the parentid name, even on those rows where the parentid is 0.

Can anyone put me out of misery and post the query here? Any link to some good & easy to follow tutorials on joins will also do the trick.

Regards, Zoreli

2 
SELECT categoryname
  FROM faq_categories AS t
 WHERE EXISTS
       ( SELECT 'oh frabjous day' 
           FROM faq_categories
          WHERE parentid = t.catid )
3

Hi Rudy

Thanks for fast reply, but this code reutrn only the results where parentid is not 0. What I am trying to achieve is to get all results with parent name in it, including the records where parent name is empty.

Any solution?

Regards, Zoreli

4

get all results with parent name in it? that would be every row in the table

i took a guess after your first post that you wanted all parents, i.e. rows which had another row referencing them via parentid

after your clarification, i’m pretty sure i don’t understand what you want at all

5

Hi

The field parentid is referencing the catid.

For example, let say that I have the following categories

catid, categoryname parentid

1 fruits 0
2 vegetables 0
3 bananas 1
4 carrots 2

I am trying to get those results:

catid categoryname parentname (parentid)

1 fruits 0
2 vegetables 0
3 bananas frutis
4 carrots vegetables

I hope this clarify what I am trying to achieve.

Regards, Zoreli

6 
SELECT child.catid 
     , child.categoryname 
     , COALESCE(parent.categoryname,'0') AS parentname 
  FROM faq_categories AS child
LEFT OUTER
  JOIN faq_categories AS parent
    ON parent.catid = child.parentid
7

Hi

Thanks a ton. As soon as I can, I will buy your book man. I will need it. I wouldn’t be able to write this query for a zillion years without your help. Thanks for your time & willing to share your knowledge.

Regards, Zoreli