MySQL Forums
Forum List  »  Newbie

Re: Complex JOIN
Posted by: Andrew Clarke
Date: July 12, 2015 02:13AM

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

Options: ReplyQuote


Subject
Written By
Posted
July 11, 2015 01:30AM
July 11, 2015 08:47AM
Re: Complex JOIN
July 12, 2015 02:13AM
July 12, 2015 09:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.