Join multiple tables in MySQL

Question

I have three tables

sem1
--------------------------
roll | sub1 | sub2 | gpa |
--------------------------
001  | 98   | 99   | 8.5 |
002  | 99   |100   | 9.9 |
--------------------------

sem2
--------------------------
roll | sub3 | sub4 | gpa |
--------------------------
001  | 88   | 87   | 8.1 |
002  | 89   | 90   | 9.0 |
-------------------------- 

sem3
--------------------------
roll | sub5 | sub6 | gpa |
--------------------------
001  | 85   | 75   | 8.5 |
002  | 90   | 80   | 8.9 |
--------------------------

I want to fetch only the roll and gpa columns in all three tables of a particular roll number(say 001) like this.

-------------------------
|roll | gpa | gpa |gpa  |
-------------------------
| 001 | 8.5 | 8.1 | 8.5 |
-------------------------

I wrote the following query

select roll,gpa,gpa,gpa 
    from sem1 natural 
    join sem2 natural 
    join sem3 
    where roll=001;

But it doesn't seems to work. Any suggestions?

Answer 1

With a natural join you have to have a unique column in every table - you don't , gpa is the same so the natural join is on roll and gpa an because gpa differs you get nothing.

if you tables looked like this

DROP TABLE IF EXISTS SEM1,SEM2,SEM3;
CREATE TABLE SEM1(roll INT primary key, sub1 INT, sub2 INT, gpa DECIMAL(10,2));
CREATE TABLE SEM2(roll INT primary key, sub3 INT, sub4 INT, gpa2 DECIMAL(10,2));
CREATE TABLE SEM3(roll INT primary key, sub5 INT, sub6 INT, gpa3 DECIMAL(10,2));

INSERT INTO sem1 VALUES
(001  , 98   , 99   , 8.5 ),
(002  , 99   ,100   , 9.9 );

INSERT INTO sem2 VALUES
(001  , 88   , 87   , 8.1 ),
(002  , 89   , 90   , 9.0 );

INSERT INTO sem3 VALUES
(001  , 85   , 75   , 8.5 ),
(002  , 90   , 80   , 8.9 );

Then your query (as amended) would produce a result -

select roll,gpa,gpa2,gpa3 
    from sem1 natural 
    join sem2 natural 
    join sem3 
    where roll=001;

+------+------+------+------+
| roll | gpa  | gpa2 | gpa3 |
+------+------+------+------+
|    1 | 8.50 | 8.10 | 8.50 |
+------+------+------+------+
1 row in set (0.001 sec)

For clarity it is best practice to qualify your column names for example

select SEM1.roll,SEM1.gpa,SEM2.gpa2,SEM3.gpa3 
from sem1 
natural join sem2 
natural join sem3 
where SEM1.roll=001;

But don't use a natural joins use a left joins for example

select SEM1.roll,SEM1.gpa,SEM2.gpa2,SEM3.gpa3 
from sem1 
left join sem2 on sem1.roll = sem2.roll
left join sem3 on sem1.roll = sem3.roll
where SEM1.roll=001;

but even this is not sufficiently robust in my view relying as it does on sem1 driving and always containing all the distinct roll numbers contained in sem2 and sem3. A more ribust approach would be to get all the distinct roll numbers from all 3 tables and drive from that.