Sometimes we wonder when to use Self joins. I was getting confused regarding its usage as a newbie. Sometimes, they are very useful as it can be used in place of nested queries maintaining the efficiency of our application. I would like to share one scenario where it was very useful to me.

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           | kevin@hotmail.com
2   | 1           | 2           | kev4*&hg
3   | 1           | 3           | news
4   | 2           | 1           | jeffery@stacenter.com
5   | 2           | 2           | 85_sd(*&
6   | 2           | 3           | magazine
7   | 3           | 1           | john@yahoo.com
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    | kevin@hotmail.com  |  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, ‘kevin@hotmail.com’);
INSERT INTO `answers` VALUES (2, 1, 2, ‘kev4*&hg’);
INSERT INTO `answers` VALUES (3, 1, 3, ‘news’);
INSERT INTO `answers` VALUES (4, 2, 1, ‘jeffery@stacenter.com’);
INSERT INTO `answers` VALUES (5, 2, 2, ’85_sd(*&’);
INSERT INTO `answers` VALUES (6, 2, 3, ‘magazine’);
INSERT INTO `answers` VALUES (7, 3, 1, ‘john@yahoo.com’);
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