Re: Complex JOIN
Thanks Peter, it looks like it could be what I'm after. Getting a little lost with my u, x, us etc. though.
Here are my tables if that helps you help me......
CREATE TABLE IF NOT EXISTS `schools` (
`id` int(3) NOT NULL,
`name` varchar(50) NOT NULL,
`image` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `skills` (
`code` varchar(4) NOT NULL,
`type` varchar(16) NOT NULL,
`cat` varchar(20) NOT NULL,
`descr` varchar(150) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`username` varchar(35) NOT NULL,
`password` varchar(25) NOT NULL,
`firstname` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
`email` varchar(40) NOT NULL,
`school` varchar(50) NOT NULL,
`teacher` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `school` (`school`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
CREATE TABLE IF NOT EXISTS `user_skills` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`skillcode` varchar(4) NOT NULL,
`userid` int(3) NOT NULL,
`date` datetime NOT NULL,
`evidence` text NOT NULL,
PRIMARY KEY (`id`),
KEY `skillcode` (`skillcode`,`userid`),
KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;
Ultimately I'd like a tracking grid / table with users' names down the side and skill codes across the top. Where a user has evidences a skill this will appear in the correct row and column and where they haven't yet evidenced the skill it will remain blank...
Not sure whether one query will do this or whether I need to to read into a multidimensional array using multiple queries?
Thanks again,
Regards,
Andrew