I have five tables as listed below.

Question: is it possible to join all five tables and output the result as show below?

<table>
    <tr>
        <th>Subject</th>
        <th>Last update (date from table. nr.3)</th> 
        <th>Last update (date from table. nr.4)</th>
        <th>Last update (date from table. nr.5)</th>
    </tr>
    <tr>
        <td>Mathematics</td>
        <td>2014-06-01</td>
        <td>2014-07-05</td>
        <td>2014-03-15</td>
    </tr>
    <tr>
        <td>English</td>
        <td>2014-01-01</td>
        <td>2014-02-11</td>
        <td>2014-04-21</td>
    </tr>
    <tr>
        <td>German</td>
        <td>2014-01-02</td>
        <td>2014-05-30</td>
        <td>2014-07-03</td>        
    <tr>   
</table>

Table (containing subject id and subject name)

 CREATE TABLE IF NOT EXISTS `subject` (
      `subject_id` int(3) NOT NULL AUTO_INCREMENT,
      `subject_name` varchar(50) COLLATE utf8_swedish_ci NOT NULL,
      PRIMARY KEY (`amne_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=100 ;

Table (containing subjects that not will be read current semester)

    CREATE TABLE IF NOT EXISTS `reading_subject` (
      `read_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
      `class` varchar(3) COLLATE utf8_swedish_ci NOT NULL,
      `subject_id` int(3) NOT NULL,
      `semester` varchar(2) COLLATE utf8_swedish_ci DEFAULT NULL,
       PRIMARY KEY (`read_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=25 ;

Table (nr. 3, containing information regarding each assessment)

    CREATE TABLE IF NOT EXISTS `kunskapskrav_klick` (
      `kun_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `subject_id` int(3) NOT NULL,
      `student_id` int(3) NOT NULL,
      `teacher_id` int(3) NOT NULL,
      `semester` varchar(2) COLLATE utf8_swedish_ci NOT NULL,
      `date_a` date NOT NULL,
      PRIMARY KEY (`kun_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=45699 ;

Table (nr.4, containing assessment from teacher)

CREATE TABLE IF NOT EXISTS `assess_teacher` (
  `kuns_id_teacher` int(10) NOT NULL AUTO_INCREMENT,
  `subject_id` int(3) NOT NULL,
  `student_id` int(10) NOT NULL,
  `teacher_id` int(3) NOT NULL,
  `semester` varchar(25) COLLATE utf8_swedish_ci NOT NULL,
  `date` varchar(25) CHARACTER SET latin1 NOT NULL DEFAULT '',
   PRIMARY KEY (`kuns_id_teacher`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=7560 ;

Table (nr.5, containing text from student)

 CREATE TABLE IF NOT EXISTS `assess_student` (
      `kuns_id_student` int(10) NOT NULL AUTO_INCREMENT,
      `subject_id` int(3) NOT NULL,
      `student_id` int(3) NOT NULL,
      `teacher_id` int(3) NOT NULL,
      `date` date NOT NULL,
    PRIMARY KEY (`kuns_id_student`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=6142 ;

Recommended Answers

All 2 Replies

As long as each table has one column which refers it to another table, through which a linkage can be made, you can join them all.
It doesn't need to be the same column in all tables as long as a chain of connectivity can be made.
In your case it looks like you can use teacher_id/teacher_id to refer to most of the tables and subject_id to link to the subjects table.

Member Avatar for diafol
SELECT ...fields... FROM firstTableName AS Alias1 
    INNER JOIN secondTableName AS Alias2 ON Alias1.fieldX = Alias2.fieldY
    INNER JOIN thridTableName AS Alias3 ON Alias2.fieldG = Alias3.fieldK ...

You just keep adding (inner) join/on clauses

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.