How to get data from one table based on a data from another table

I have a two tables

 tblstudent (StudentID (PK), StudentNumber, Name & Surname)

and

 tblfingerprint (ID (PK), StudentID (FK), Template) 

I have a web service that selects a Template based on their ID. However, I want to be able to select the template from tblFingerprint based on the given StudentNumber and not StudentID in tblStudent.

I have the following code so far. I'm not sure if I need to use leftjoin or innerjoin?

[WebMethod]
public Verification StuVerification(Student student)
{
    cn.Open();
    SqlCommand com = new SqlCommand("SELECT Template FROM tblFingerprint WHERE  ID = '"+ student.StudentNumber + "'", cn);

    //SqlCommand com = new SqlCommand("SELECT Template FROM tblFingerprint WHERE ID = '" + ID + "'", cn);
    //com.Parameters.AddWithValue("@Template", Template);
    SqlDataReader sr = com.ExecuteReader();
    while (sr.Read())
    {
        Verification verification = new Verification()
        {
            StudentID = sr.GetInt32(0),
            StudentNumber = sr.GetString(1),
            Name = sr.GetString(2),
            Surname = sr.GetString(3),
        };
        cn.Close();
        return verification;

    }

    cn.Close();
    return new Verification();

}

Answers


Use an inner join between the two tables, but as usual use a parameterized query

string cmdText = @"SELECT s.StudentID, s.StudentNumber, s.Name, s.Surname, f.Template 
                   FROM tblStudent s INNER JOIN tblFingerprint f
                   ON t.StudentID = f.StudentID 
                   WHERE s.StudentNumber = @numb";
SqlCommand com = new SqlCommand(cmdText, cn);
com.Parameters.AddWithValue("@numb", student.StudentNumber);

In an INNER JOIN only the records of the first table that have corresponding records on the second table are returned (in your case only the students that have a corresponding record in the tblFingerprint table) In a LEFT JOIN every records of the first table are returned with the corresponding values of the second table. If any student has no record in the tblFingerprint, the Template field will be null

Also, note that you try to read 4 fields from your datareader, but the actual query contains just the Template field. Thus I have added the missing fields to your query


First of all, if you did a little search for yourself you would find thousands of pages with information about it.

OT: you can join the table

SELECT Template FROM tblFingerprint WHERE ID = '"+ student.StudentNumber + "'


SELECT Template 
FROM tblFingerprint F
INNER JOIN tblStudent S
ON F.StudentID = S.StudentID
WHERE S.StudentNumber = '"+ student.StudentNumber + "'"

Hint: Work with parameters, in this example there a major risk for SQL Injection


JOIN is equivalent to INNER JOIN on SQL Server so just use the following. I've added aliases to the table names to make it less verbose.

I've based it on your example so I have to assume that StudentNumber is a string field. If not then just remove the string delimiters from your query.

"SELECT Template FROM tblFingerprint fp 
JOIN tblstudent s ON fp.StudentID = s.StudentID
WHERE s.StudentNumber = '"+ student.StudentNumber + "'"

Hope this helps. Al.


Need Your Help

Flex 3 debugging not working with FIREFOX 3

apache-flex flex3

Flex 3 debugging not working with FIREFOX 3 (under vista)

Sending a variable as a parameter in second stored proc sql

sql sql-server stored-procedures parameter-passing ssms

I was wondering if this was possible in SSMS. So I have a stored proc that's within another stored proc. Is it possible to pass the variables of one stored proc as a parameter in the second one. I