SELECT Columns FROM different Tables using WHERE clause

[gbhs]

Hi to all,
As a newbie, I am not able to have the following columns into a datagrid. (see database diagram attached)
(I don't want to select ID columns because users may not be able to identify the students). I don't master JOINS .

Infact I want to select all students from tbl_scores who are in a class(level) called F1A and offer the subject Maths

SELECT SubjectName, ClassName, StudentName, Seq1
FROM Tbl_Scores --etc
WHERE ClassName='F1A' AND Subjectname='Maths'

There should be exactly 10 records selected.
But I get Multiple similar records
Can some help by refering to diagram?
thanks

[pixelsoul]

This may help you
http://www.mysqltutorial.org/mysql-distinct.aspx

[IIM]

can you show your table structure?

[gbhs]

Here is an attachment of my tables diagram shows the table structures. I have used the file menu here to attach it.
Primary keys are as shown and any other fields ending with ID are foreign keys.
Thanks

[pixelsoul]

Was that down voted because it doesn't work for you, or because you don't like links?

And, you haven't attached anything.

[pritaeas]

My bad, leftover from some API testing, although you did link to MySQL instead of MSSQL. ;)

[pixelsoul]

@pritaeas - lol.. Now I am a little concerned.

[gbhs]

I'm new to this forum and still learning how to go about menus. surprise it didnot attach.
My table structure

Tbl_Classes(ClassID PK, ClassName)
Tbl_Students(StudentID PK, StudentNames,ClassID FK)
Tbl_Scores(ScoresID PK,StudentID FK,ClassID FK, SubjectID FK, Seq1)
Tbl_Subjects(SubjectID PK, SubjectName)

Also tables are linked(one-to-many related) as follows:
Tbl_Classes -------> Tbl_Students --------> Tbl_Scores
Tbl_Subjects -------> Tbl_Scores

The 4 fields I want to select are below and I expect to get 10 records.
The WHERE clause should have classname and subjectname (not classID and SubjectID).

***

SELECT SubjectName, ClassName, StudentName, Seq1
FROM Tbl_Scores      --etc
WHERE ClassName='F1A' AND Subjectname='Maths'

***

Thanks

[pritaeas]

Show your full query, instead of "--etc".

[IIM]

You have to use inner join.I am not sure but this query might work.Join the table on the relation ou have told.And your query i ready to go.

Select a.seq1,b.classname,c.subjectname,d.studentNames
From tbl_scores as a
Inner Join tbl_classes as b on a.classid= b.classid
Inner Join tbl_subjects as c on a.subjectid = c.subjectid 
inner Join tbl_students as d on a.classid = b.classid

[gbhs]

I just modified it as follows and it worked

use SchoolDbase

Select a.Seq1,b.classname,c.subjectname,d.studentNames
    From tbl_scores as a
    Inner Join tbl_classes as b on a.classid= b.classid
    Inner Join tbl_subjects as c on a.subjectid = c.subjectid
    inner Join tbl_students as d on (a.classid = b.classid and  a.StudentID =d.StudentID )

    where a.classid=1 and a.subjectid = 1

But I would like the last line to be

WHERE b.classname='f1a' AND c.subjectname='english' 

(the passed strings are from the respective tables). When I run it , no record is retrieved and no error.
How can I make this work?
Thanks

[gbhs]

OH
I just figured out how to do it. Its fine now.Like so

use SchoolDbase

Select a.Seq1,b.classname,c.subjectname,d.studentNames,d.Gender 
From tbl_scores as a
Inner Join tbl_classes as b on a.classid= b.classid
Inner Join tbl_subjects as c on a.subjectid = c.subjectid
inner Join tbl_students as d on (a.classid = b.classid and  a.StudentID =d.StudentID )

where a.classid=(select tbl_classes.ClassID from tbl_classes where tbl_classes.ClassName='f1a') 
and a.subjectid =(select tbl_subjects.subjectid from tbl_subjects where tbl_subjects.SubjectName ='english language')

Thanks