My database has 2 tables, milestones, user_milestones
milestones - has info for all the milestones that are available on the website
CREATE TABLE `milestones` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`how` longtext NOT NULL COMMENT 'Use \\n for new line, not enter.',
`url` varchar(255) NOT NULL COMMENT 'URL for the completed image',
PRIMARY KEY (`ID`),
KEY `url` (`url`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
user_milestones - lists all milestones the user has gained
CREATE TABLE `user_milestones` (
`mid` int(11) NOT NULL COMMENT 'Milestone ID',
`uid` int(11) NOT NULL COMMENT 'User ID',
PRIMARY KEY (`mid`,`uid`),
KEY `uid` (`uid`),
CONSTRAINT `user_milestones_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_milestones_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `milestones` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I'm trying to get a list of all the milestones available and which ones a user has achieved.
I've got this sql
SELECT m.name, m.how, m.url, (
CASE u.mid
WHEN u.mid IS NULL
THEN 0
ELSE 1
END
) AS completed, u.mid, u.uid
FROM milestones AS m
LEFT JOIN user_milestones AS u ON u.mid = m.ID
AND u.uid =1
ORDER BY m.ID ASC
which other than the case part, is showing the right data. The case bit is coming up as 1 even if u.mid is null, as you can see from the screenshot below
http://calona.net/images/calona.jpg
Anyone know what the problem is
