conditional joining two table's data in one oledb query, is this possible?

[pmouseca]

Hello all,
I have two tables, one call Assignments, the other call Grades.
In Assignment, the columns are:
Asn_ID, Asn_Name, Asn_MaxGrade

In Grades, the columns are:
Grade_ID, Grade_Assignment, Grade_Student, Grade_Value

There exist a one-to-many relationship on Asn_ID = Grade_Assignment

My question is this: the grade of a student might not exist in the
grade table, i would like to pull all rows from the assignment table,
and the grade_value from the Grades table IF it exists, otherwise, I
would like to have a new column set to NULL (representing the missing
grade)

I do not know how to achieve this via a single query, thus I have
implemented a somewhat inefficient way in C#:
First, Pull all assignment rows in a select query, load this into a
data table.
Then create a new column in the data table, called Asn_Grades
Then for each row in the table, query the database once again for the
grade entry,
if there is a grade for the record, add it to the data table's new
column,
otherwise, add "-" to the new column
Finally, load this datatable into a dataview for binding.

As you can see, this method requires multiple queries every time the
result needs to be displayed. Is there a single query that can
achieve these?

Regards,

PQ

[OldPro]

On Sep 3, 11:05 pm, [e-mail] wrote:

Hello all,
I have two tables, one call Assignments, the other call Grades.
In Assignment, the columns are:
Asn_ID, Asn_Name, Asn_MaxGrade

In Grades, the columns are:
Grade_ID, Grade_Assignment, Grade_Student, Grade_Value

There exist a one-to-many relationship on Asn_ID = Grade_Assignment

My question is this: the grade of a student might not exist in the
grade table, i would like to pull all rows from the assignment table,
and the grade_value from the Grades table IF it exists, otherwise, I
would like to have a new column set to NULL (representing the missing
grade)

I do not know how to achieve this via a single query, thus I have
implemented a somewhat inefficient way in C#:
First, Pull all assignment rows in a select query, load this into a
data table.
Then create a new column in the data table, called Asn_Grades
Then for each row in the table, query the database once again for the
grade entry,
if there is a grade for the record, add it to the data table's new
column,
otherwise, add "-" to the new column
Finally, load this datatable into a dataview for binding.

As you can see, this method requires multiple queries every time the
result needs to be displayed. Is there a single query that can
achieve these?

Regards,

PQ

Use a LEFT JOIN.