Problem:
We have three table ‘questions’, ‘answers’ and ‘questions’. ‘Questions’ table is not used, have placed it as an reference. About relationships, Attendee table has one-many relationship with answers table, which contains the foreign key fk_attid to attendee table resp. The tables have the datas as mentioned below.
Questions Table
qid | question | system_name | is_required | status
1 | username | uname | Y | 1 2 | password |password | Y | 1
3 | How did you know abt me ? | know_me | Y | 1
Answers Table
aid | fk_attid | fk_qid | answer
1 | 1 | 1 | [email protected] 2 | 1 | 2 | kev4*&hg 3 | 1 | 3 | news 4 | 2 | 1 | [email protected] 5 | 2 | 2 | 85_sd(*& 6 | 2 | 3 | magazine 7 | 3 | 1 | [email protected] 8 | 3 | 2 | 1*&)*kj
9 | 3 | 3 | telephone
Attendee table
attid | lname | fname | address1 | address2 | status
1 | Gyberg | Kevin | San diago | NULL | 1 2 | Maher | Jeffery | Louisiana | NULL | 1 3 | Doe | John | Texas | NULL | 1 4 | Richmond | Robert | chattanoga | NULL | 1 5 | Jobs | Steve | Toronto | NULL | 1 6 | Trovalds | Linus | Manchester | NULL | 1 7 | Ritchie | Dennis| Philadelphia | NULL | 1
8 | Keringham| NULL | San diago | NULL | 1
Now, what do we want ?
Need to display the records in the manner mentioned below. Mark the fields username (which is the answer of question1) and password
(which is the answer of question2) but they need to come in an single row.
attid | lname | fname | username | password
1 | Gyberg | Kevin | [email protected] | kev4*&hg
Solution:
I have exported the tables from my mysql database , anybody can import and analyse in their local system.
Steps
Step 1: Create answers table using the following statements
CREATE TABLE `answers` ( `aid` int(11) NOT NULL auto_increment, `fk_attid` int(11) NOT NULL, `fk_qid` int(11) NOT NULL, `answer` varchar(255) collate latin1_general_ci NOT NULL, PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;
— — Dumping data for table `answers`
—
INSERT INTO `answers` VALUES (1, 1, 1, ‘[email protected]’); INSERT INTO `answers` VALUES (2, 1, 2, ‘kev4*&hg’); INSERT INTO `answers` VALUES (3, 1, 3, ‘news’); INSERT INTO `answers` VALUES (4, 2, 1, ‘[email protected]’); INSERT INTO `answers` VALUES (5, 2, 2, ’85_sd(*&’); INSERT INTO `answers` VALUES (6, 2, 3, ‘magazine’); INSERT INTO `answers` VALUES (7, 3, 1, ‘[email protected]’); INSERT INTO `answers` VALUES (8, 3, 2, ‘1*&)*kj’);
INSERT INTO `answers` VALUES (9, 3, 3, ‘telephone’);
— ——————————————————–
Step 2: Create attendee table using the following statements — — Table structure for table `attendee`
—
CREATE TABLE `attendee` ( `attid` int(11) NOT NULL auto_increment, `lname` varchar(255) collate latin1_general_ci NOT NULL, `fname` varchar(255) collate latin1_general_ci NOT NULL, `address1` varchar(255) collate latin1_general_ci NOT NULL, `address2` varchar(255) collate latin1_general_ci NOT NULL, `status` tinyint(4) NOT NULL default ‘1’, PRIMARY KEY (`attid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=9 ;
— — Dumping data for table `attendee`
—
INSERT INTO `attendee` VALUES (1, ‘Gyberg’, ‘Kevin’, ‘San diago’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (2, ‘Maher’, ‘Jeffery’, ‘Louisiana’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (3, ‘Doe’, ‘John’, ‘Texas’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (4, ‘Richmond’, ‘Robert’, ‘chattanoga’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (5, ‘Jobs’, ‘Steve’, ‘Toronto’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (6, ‘Trovalds’, ‘Linus’, ‘Manchester’, ‘NULL’, 1); INSERT INTO `attendee` VALUES (7, ‘Ritchie’, ‘Dennis’, ‘Philadelphia’, ‘NULL’, 1);
INSERT INTO `attendee` VALUES (8, ‘Keringham’, ‘NULL’, ‘San diago’, ‘NULL’, 1);
— ——————————————————–
Step 3: Create questions table using the following statements
— — Table structure for table `questions`
—
CREATE TABLE `questions` ( `qid` int(11) NOT NULL auto_increment, `question` varchar(255) collate latin1_general_ci default NULL, `system_name` varchar(255) collate latin1_general_ci default NULL, `is_required` varchar(255) collate latin1_general_ci default NULL, `status` tinyint(4) NOT NULL default ‘1’, PRIMARY KEY (`qid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;
— — Dumping data for table `questions`
—
INSERT INTO `questions` VALUES (1, ‘username’, ‘uname’, ‘Y’, 1); INSERT INTO `questions` VALUES (2, ‘password’, ‘password’, ‘Y’, 1);
INSERT INTO `questions` VALUES (3, ‘How did you know about me ?’, ‘know_me’, ‘Y’, 1);
Step 4: The self join query that will give us our solution
SELECT att1.`attid` , att1.`lname` , att1.`fname` , att1.`address1` , ans1.answer username, ans2.answer PASSWORD FROM attendee att1 LEFT JOIN answers ans1 ON att1.attid = ans1.fk_attid AND ans1.fk_qid =1 INNER JOIN answers ans2 ON att1.attid = ans2.fk_attid
AND ans2.fk_qid =3