selecting a column from ms-access database which has linked tables

Question

I am building a windows form C# app. and I use oleDb for linking access database to my app. the problem is, My access database has two tables (students,courseCodes) and one column of the "students" table(courseName) is linked to one in the "courseCode" table (the "courseCode" table contains course codes for example course code 1 is Static and I use code 1 in the "students" table for displaying Statics) now when I want to select column containing Statics using

"SELECT DISTINCT courseName FROM students";

I got the "1" instead "Statics" is there any way to retrieve "Statics" instead "1"?

duffymo · Accepted Answer · 2014-08-08 12:15:36Z

4

I'd say your naming convention is misleading and confusing. The column should be courseIndex, not courseName.

Do a JOIN, of course (no pun intended). This query will return the distinct course names that a given student has signed up for.

select distinct courseCode.courseName
from student
join courseCode
on student.courseId = courseCode.id
where student.id = ?

Please adjust for your schema details.

Personally I think this is a poor design. A student can sign up for many courses, and a course can have many students. This is a many-to-many relationship. You need a join table; sounds like you only have a foreign key one-to-many relationship here.

answered Aug 8, 2014 at 12:15

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander1991 Over a year ago

Thank you so much duffymo you're so right I'll consider this in my design thank you for your advice.

Collectives™ on Stack Overflow

selecting a column from ms-access database which has linked tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related