CODEWITHSUNDEEP
sqlms-accessjoinunion
Muhammad Rizwan
Muhammad Rizwan

Reputation: 141

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?

Upvotes: 0

Views: 50

Answers (3)

Applecore
Applecore

Reputation: 4099

I think that the SQL below is what you are after:

SELECT s.RollNo, S.SName, S.DOB, C.Class, S.Picture
FROM Students S, Classes C
WHERE S.RollNo=[Enter roll no];

Which gives:

enter image description here

Regards,

Upvotes: 0

Raju
Raju

Reputation: 75

See Here and Try It:

select s.RollNo,s.SName,s.DOB,c.Class,s.Picture
from Students s
Left join Classes c on c.ClassID   = s.ClassID
where s.RollNo=101;

Here Exactly What You Want:

enter image description here

Upvotes: 0

forpas
forpas

Reputation: 164089

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

Upvotes: 1

Related Questions