I thought Guido wanted the actual contents of the table.
Here’s show create table:
CREATE TABLE `albums` (
`album_id` int(11) NOT NULL AUTO_INCREMENT,
`album_url` varchar(64) NOT NULL,
`album_date` date NOT NULL,
`parent_album` int(11) DEFAULT NULL,
`album_name` varchar(64) NOT NULL,
`album_avatar` varchar(64) DEFAULT NULL,
`album_desc` varchar(254) NOT NULL,
PRIMARY KEY (`album_id`),
KEY `album_url` (`album_url`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
And here’s the dump:
INSERT INTO `albums` (`album_id`, `album_url`, `album_date`, `parent_album`, `album_name`, `album_avatar`, `album_desc`) VALUES
(1, 'temagami', '2013-01-07', NULL, 'Temagami', 'img_1013.jpg', 'Temagami Pictures'),
(2, 'summer_2008', '2008-07-25', 1, 'Summer 2008', '', '2008 Temagami Pics'),
(3, 'summer_2010', '2010-07-26', 1, 'Summer 2010', 'img_1044.jpg', '2010 Temagami Pics'),
(5, 'summer_2012', '2012-07-28', 1, 'Summer 2012', '', '2012 Temagami pics'),
(7, 'greece2010', '2013-01-08', NULL, 'Greece, 2010', 'img_1491.jpg', 'Our Trip to Greece'),
(11, 'summer_2007', '2007-07-31', 1, 'Summer 2007', NULL, 'Pics from Temagami, summmer 2007'),
(12, 'uk2009', '2009-08-23', NULL, 'London, Glasgow, Edinburgh', NULL, 'Our trip to London and Scotland '),
(13, 'u2360', '2009-08-23', NULL, 'U2 360 Tour', NULL, 'Following our favorite band '),
(14, 'bikes', '2013-01-13', NULL, 'Bikes', NULL, 'Our bikes and cycling photos.'),
(17, 'scotland', '2009-08-23', 13, 'Hampden Park - Glasgow, Scotland', NULL, ''),
(21, 'toronto', '2013-01-13', 13, 'Rogers Center - Toronto, ON', NULL, 'Rogers Center - Toronto, ON'),
(22, 'landover', '2013-01-13', 13, 'FedEx Field - Landover, MD', NULL, ''),
(23, 'raleigh', '2013-01-13', 13, 'Carter-Finley Stadium - Raleigh, NC', NULL, ''),
(26, 'london', '2013-01-14', 13, 'Wembley Stadium - London, England', NULL, 'Wembley Stadium - London, England');
The “holes” are probably columns where I don’t have data. Like, there’s a column called “album_avatar” which is the thumbnail pic shown when you view the main gallery page. If that’s blank, the gallery page picks the first thumbnail in the album to show. If I don’t like that pic, I can go in and pick another one, and that’s what goes in that column.
Other “holes” are things like album description that I just haven’t filled out yet. Maybe I should default all those to NULLs to avoid the holes.
But no, there are no children without parents, but there are parents with no children.
Now that I understand COALESCE, I’ve tried playing with that query but my changes always make things worse. I think maybe while I understand what COALESCE does, I’m still having a hard time visualizing it.