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

can you show your table structure?

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

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

And, you haven't attached anything.

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

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

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

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

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.