By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Sep 4 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sep 3, 11:05 pm, pmous...@gmail.com 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.

Sep 4 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.