SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    self join with where clauslue problem

    Hi

    This is my table:

    Code:
    --
    -- 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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by zoreli View Post
    I need to query the database so I can find the parentid name, even on those rows where the parentid is 0.
    Code:
    SELECT categoryname
      FROM faq_categories AS t
     WHERE EXISTS
           ( SELECT 'oh frabjous day' 
               FROM faq_categories
              WHERE parentid = t.catid )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •