Data Joining of two Tables MS Access SQL

I have two tables named – 1) Students 2) Classes. ClassID is Primary Key of Classes table and Foreign Key of Students Table.

I want to access one row from the Students table having specfic RollNo (This RollNo is given by user input) and all rows from Classes Table.

Students Table is given as:-

RollNo   |   SName   |    DOB     |   ClassID   |   Picture
--------------------------------------------------------------
101      | AAAA      | 22/09/2012 | 1           | attachment
102      | BBBB      | 22/09/2010 | 2           | attachment
103      | CCCC      | 22/09/2011 | 1           | attachment
104      | DDDD      | 22/09/2010 | 4           | attachment

RollNo is Primary Key. And Last Field of Students is Picture attachment.

Classes Table is given as :-

ClassID   |   Class
--------------------
1         | One     
2         | Two      
3         | Three    
4         | Four

I want the output like this

Output

RollNo   |   SName   |    DOB     |   Class   |   Picture
--------------------------------------------------------------
         |           |            | One       | 
102      | BBBB      | 22/09/2010 | Two       | attachment
         |           |            | Three     | 
         |           |            | Four      |

What is the query to get data like this?

Answer

You need a left join of Classes to Students:

select s.RollNo, s.SName, s.DOB, c.Class, s.Picture
from classes as c left join (select * from students where rollno = 102) as s
on s.classid = c.classid

Output

Advertisement

Answer