#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,566
    Rep Power
    171

    Why is this query returning empty results?


    Why is this query returning empty results?
    Code:
    SELECT cat.name AS supercategory,
           sub.name AS category
    FROM   categories AS cat
           INNER JOIN categories AS sub
                   ON sub.parent = cat.name
    ORDER  BY cat.name,
              sub.name;
    Code:
    CREATE TABLE IF NOT EXISTS `categories` (
      `category` varchar(9) NOT NULL,
      `name` varchar(37) NOT NULL,
      `parent` varchar(9) DEFAULT NULL,
      PRIMARY KEY (`category`),
      KEY `parent_ix` (`parent`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `categories`
    --
    
    INSERT INTO `categories` (`category`, `name`, `parent`) VALUES
    ('blog', 'Log on to My Blog', 'personal'),
    ('humor', 'Humorous Anecdotes', 'personal'),
    ('angst', 'Stories from the Id', 'personal'),
    ('advice', 'Gentle Words of Advice', 'personal'),
    ('science', 'Our Spectacular Universe', 'general'),
    ('general', 'Articles and Resources', NULL),
    ('personal', 'Personal Stories and Ideas', NULL);
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,742
    Rep Power
    9397
    Because you're using the wrong field in the JOIN condition.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by requinix
    Because you're using the wrong field in the JOIN condition.
    correct

    this is actually an example out of my book, and the book has an error right there
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,566
    Rep Power
    171
    Ok. I doubted everything else but I couldn't imagine query error in that book.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,394
    Rep Power
    1688
    Originally Posted by r937
    correct

    this is actually an example out of my book, and the book has an error right there
    Missed that in my informal proof-reading! Blame me
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo