Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Many to many relationship Query

I want to know the query I need to write for such a situation:
Say student, course tables exist. To find a list of all student details and all the courses that a particular student undertakes.
eg:
studentid1
         courseid1 ......
         courseid2 ......
studentid2
         courseid3 ......
         courseid4 ......
   
0
cgibin
Asked:
cgibin
  • 3
  • 3
  • 2
  • +1
1 Solution
 
HummusxCommented:
Ok well presumably you have an XRef table to connect them somehow..then:

SELECT Student.ID, Course.ID FROM Student INNER JOIN (StudentCourseXRef INNER JOIN Course ON StudentCourseXRef.CourseID = Course.ID) ON Student.ID = StudentCourseXRef.StudentID

I think that should do it.

0
 
nico5038Commented:
For this an additional table "StudentCourse" is needed.
Only the fields studentid and courseid are needed there and will have to be defined to be a unique key to prevent dupicate entries.

Nic;o)
0
 
HummusxCommented:
Right, that's pretty much the only way to do many-many relationships.  The StudentCourse table just provides a mapping between the Student and Course tables, where each record represents one course that one student is taking/took.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
h_mahadevanCommented:

You need another table studentcourse which maps the two table.

Otherwise create an alias which refers the student table itself while create a query
0
 
cgibinAuthor Commented:
how is it possible to to query by creating an alias? I'm not sure whether they will be a junction table so I want to know this alternative. Can you please show me an example query?
0
 
cgibinAuthor Commented:
how is it possible to to query by creating an alias? I'm not sure whether they will be a junction table so I want to know this alternative. Can you please show me an example query?
0
 
cgibinAuthor Commented:
how is it possible to to query by creating an alias? I'm not sure whether they will be a junction table so I want to know this alternative. Can you please show me an example query?
0
 
h_mahadevanCommented:
My guess is that!

how do you find who is the manager of whom in emp table in oracle.
you create an alias for emp table.

Try. but i am not sure
0
 
HummusxCommented:
You are going to need the junction table in order to do many-to-many relationships.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now