Newbie question , get data from table

Question

Sorry to ask such simple question here , as I am new to database , the below is example , in this example , there are primary key and foreign key , If I want to extract the data of what subject the student ( ID ) taken , how to make it ? thanks a lot

SQL Table 1

==========

student ID student Name

0001 Peter

0002 Mary

0003 John

SQL Table 2

=====

Student Name Subject

Peter English

Mary Maths

Join Biology

What I want to get is as below

=============

0001 English

0002 Maths

0003 Biology

Answer 1

Hi @chan patrick
We usually use JOIN statement to combine data or rows from two or more tables based on a common field between them.
Different types of Joins are as follows: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN
If you only want to display Student IDs that have corresponding Subjects in Table2, then use INNER JOIN:

SELECT [Student ID],[Subject]  
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.[Student Name] = T2.[Student Name]  

If you want to display all the Student IDs, even if there is no corresponding Subject in Table2, then you can use LEFT JOIN:

SELECT [Student ID],[Subject]  
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.[Student Name] = T2.[Student Name]  

You can refer this blog for more details and examples: SQL | Join (Inner, Left, Right and Full Joins)

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Welome to Microsoft QA forums!

About your question, please try the following query:

SELECT [Student ID], [Subject]  
FROM [SQL Table 1] t1 INNER JOIN [SQL Table 2] t2  
ON t1.[Student Name] = t2.[Student Name]