Newbie question , get data from table

Question

Newbie question , get data from table

patrick chan

Old Hand

Points: 338
More actions
July 24, 2022 at 1:30 am

#4067369
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
=============
0001 English
0002 Maths
0003 Biology
- This topic was modified 2 years, 5 months ago by patrick chan.

July 26, 2022 at 9:05 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

I've modified your structure a little to make it more relational and injected some constraints to demonstrate typical referential integrity. Hopefully, it helps you with this. Post back with any questions.

DROP TABLE IF EXISTS #Student;

CREATE TABLE #Student
(
    StudentId CHAR(4) NOT NULL PRIMARY KEY CLUSTERED
   ,FirstName VARCHAR(100) NOT NULL
);

DROP TABLE IF EXISTS #Subject;

CREATE TABLE #Subject
(
    SubjectId INT NOT NULL PRIMARY KEY CLUSTERED
   ,SubjectName VARCHAR(300) NOT NULL
);

DROP TABLE IF EXISTS #StudentSubject;

CREATE TABLE #StudentSubject
(
    StudentId CHAR(4) NOT NULL
   ,SubjectId INT NOT NULL
        PRIMARY KEY (
                        StudentId
                       ,SubjectId
                    )
   ,
   FOREIGN KEY (StudentId) REFERENCES #Student (StudentId)
   ,
   FOREIGN KEY (SubjectId) REFERENCES #Subject (SubjectId)
);

INSERT #Student
(
    StudentId
   ,FirstName
)
VALUES
('0001', 'Peter')
,('0002', 'Mary')
,('0003', 'John');

INSERT #Subject
(
    SubjectId
   ,SubjectName
)
VALUES
(1, 'English')
,(2, 'Maths')
,(3, 'Biology');

INSERT #StudentSubject
(
    StudentId
   ,SubjectId
)
VALUES
('0001', 1)
,('0002', 2)
,('0003', 3);

SELECT st.StudentId
      ,su.SubjectName
FROM #StudentSubject ss
    JOIN #Student st
        ON st.StudentId = ss.StudentId
    JOIN #Subject su
        ON su.SubjectId = ss.SubjectId;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

data victim Grasshopper Points: 11 More actions · Answer 2

patrick chan, while Phil's answer is totally correct, if you are an absolute beginner, his reply might be too complicated for you to understand (you should take efforts to do so, though).

Also, you would usually not join on first names here. Think about the results if there was another Peter studying English, or Maths.

Anyway, this should get your result:

select

[SQL Table 1].[student id],

[SQL Table 2].[Subject]

from [SQL Table 1] st1

join [SQL Table 2] st2

on ([st1].[Student Name] = [st2].[Student Name])

edit: I am sorry, I seem to be unable to format the code properly.

This reply was modified 2 years, 5 months ago by data victim.
This reply was modified 2 years, 5 months ago by data victim.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

data victim wrote:

patrick chan, while Phil's answer is totally correct, if you are an absolute beginner, his reply might be too complicated for you to understand (you should take efforts to do so, though).
Also, you would usually not join on first names here. Think about the results if there was another Peter studying English, or Maths.
Anyway, this should get your result:
select [SQL Table 1].[student id], [SQL Table 2].[Subject] from [SQL Table 1] st1 join [SQL Table 2] st2 on ([st1].[Student Name] = [st2].[Student Name])
edit: I am sorry, I seem to be unable to format the code properly.

Phil's answer is actually the simple final bit of code in the last SELECT. The rest of it is posting the OPs data in "Readily Consumable" format for others to play with, which is what the OP should have done originally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)