MiaBergdorfl
New Member
I have a database where I store keywords grouped into projects and data related to each keyword, then I display datagrids foreach project with one row per keyword and several columns all retrieved from the same table "data". I have 4 tables, keywords, projects, group_keywords and data. "keywords" only stores the keyword, "projects" the project name, "group_keywords" the keywords ids for the keywords assigned to that project, and "data" is where all the data foreach keyword goes, identified by a foreign key for the keywords.id, and a name column to identify the data name.Now to retrieve the keywords + all the data for a project I use this query:\[code\]SELECT * FROM `group_keywords` INNER JOIN keywords on keywords.id = keyword_id INNER JOIN data ON data.id = keywords.id WHERE `group_id` = (SELECT `id` FROM `projects` WHERE `name` = 'ProjectName'\[/code\]This gives me something like\[code\] id group_id keyword_id id keyword id name value 12 5 52 52 absorption food 52 data_name_x1 6 12 5 52 52 absorption food 52 data_name_x2 8 12 5 52 52 absorption food 52 data_name_x3 26 12 5 52 52 absorption food 52 data_name_x4 2...\[/code\]But what I want is to get:\[code\]id group_id keyword_id id keyword id data_name_x1 data_name_x2 data_name_x3 data_name_x412 5 52 52 absorption food 52 6 8 26 2...\[/code\]So I can sort and use pagination for the datagrids easly, otherwise I have no idea how to do it, because when using big data sets I can't just dump everything into an array, too much data.This is the schema:\[code\]-- ---------------------------------------------------------- Table structure for table `keywords`CREATE TABLE IF NOT EXISTS `keywords` ( `id` int(10) unsigned NOT NULL auto_increment, `keyword` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `keyword` (`keyword`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115386 ;-- ---------------------------------------------------------- Table structure for table `data`CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ---------------------------------------------------------- Table structure for table `projects`--CREATE TABLE IF NOT EXISTS `projects` ( `id` int(10) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `parent` varchar(100) default NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;-- ---------------------------------------------------------- Table structure for table `group_keywords`CREATE TABLE IF NOT EXISTS `group_keywords` ( `id` int(10) NOT NULL auto_increment, `group_id` int(10) NOT NULL, `keyword_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `group_id` (`group_id`,`keyword_id`), KEY `keyword_id` (`keyword_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119503 ;-- ---------------------------------------------------------- Constraints for table `data`--ALTER TABLE `data` ADD CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;-- ---------------------------------------------------------- Constraints for table `group_keywords`--ALTER TABLE `group_keywords` ADD CONSTRAINT `group_keywords_ibfk_1` FOREIGN KEY (`keyword_id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;\[/code\]