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

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,754
    Rep Power
    172

    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. Nosey Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,529
    Rep Power
    9414
    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,347
    Rep Power
    4281
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,754
    Rep Power
    172
    Ok. I doubted everything else but I couldn't imagine query error in that book.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,527
    Rep Power
    1753
    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