Weird many to many and one to many relationship

Answer 1

It doesn't seem to make sense. A query like:

SELECT * FROM relAC RAC
  INNER JOIN tableA A ON A.id_class = RAC.id_class 
  INNER JOIN tableC C ON C.id_class = RAC.id_class 
    WHERE A.id_class = B.id_class

could generate a set of data but inconsistent. Or maybe we are missing some important part of the information about the content and the relationships of those 3 tables.

Answer 2

This is a doable scenario. You can join a table twice in a query, usually assigning it a different alias to keep things straight.

For example:

SELECT s.name AS "student name", c1.className AS "student class", c2.className as "class list"
FROM s
JOIN many_to_many mtm ON s.id_student = mtm.id_student
JOIN c c1 ON s.id_class = c1.id_class
JOIN c c2 ON mtm.id_class = c2.id_class

This will give you a list of all students' names and "hardcoded" classes with all their classes from the many_to_many table.

That said, this schema doesn't make logical sense. From what I can gather, you want students to be able to have multiple classes, so the many_to_many table should be where you'd want to find the classes associated with a student. If the id_class entries used in table s are distinct from those in many_to_many (e.g., if s.id_class refers to, say, homeroom class assignments that only appear in that table while many_to_many.id_class refers to classes for credit and excludes homeroom classes), you're going to be better off splitting c into two tables instead.

If that's not the case, I have a hard time understanding why you'd want one class hardwired to the s table.

EDIT: Just saw your comment that this was a made-up schema to give an example. In other cases, this could be a sensible way to do things. For example, if you wanted to keep track of company locations, you might have a Company table, a Locations table, and a Countries table. The Company table might have a 1-many link to Countries where you would keep track of a company's headquarters country, but a many-to-many link through Locations where you keep track of every place the company has a store.

If you can give real information as to what the schema really represents for your client, it might be easier for us to figure out whether it's logical in this case or not.

Answer 3

Perhaps it's a lack of caffeine, but I can't conceive of a legitimate reason for wanting to do this. In the example you gave, you've got students, classes and a table which relates the two. If you think about what you want the query to do, in plain English, surely it has to be driven by either the student table or the class table. i.e.

• select all the classes which are attended by student 1245235
• select all the students which attend class 101

Can you explain the requirement better? If not, tell your customer to suck it up. Having a relationship between Students and Classes directly (A and C), seems like pure madness, you've already got table B which does that...

Answer 4

Bear in mind that the one-to-many relationship can be represented through the many-to-many, most simply by adding a field there to indicate the type of relationship. Then you could have one "current" record and any number of "history" ones.

Was the customer "requirement" phrased as given, by the way? I think I'd be looking to redefine my relationship with them if so: they should be telling me "what" they want (ideally what, in business domain language, their problem is) and leaving the "how" to me. If they know exactly how the thing should be implemented, then I'd be inclined to open the source code in an editor and leave them to it!

Answer 5

I personally never heard a requirement from a customer that would sound like:

Obtain the information from the Table B inner joining with A and C, and in the same query relate A and C in a one-many relationship

It looks like that it is what you translated the requirement to. Could you specify the requirement in plain English, as what results your customer wants to get?

Answer 6

I'm supposing that s.id_class indicates the student's current class, as opposed to classes she has taken in the past.

The solution shown by rcar works, but it repeats the c1.className on every row.

Here's an alternative that doesn't repeat information and it uses one fewer join. You can use an expression to compare s.id_class to the current c.id_class matched via the mtm table.

SELECT s.name, c.className, (s.id_class = c.id_class) AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
  JOIN c ON (c.id_class = mtm.id_class);

So is_current will be 1 (true) on one row, and 0 (false) on all the other rows. Or you can output something more informative using a CASE construct:

SELECT s.name, c.className, 
  CASE WHEN s.id_class = c.id_class THEN 'current' ELSE 'past' END AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
  JOIN c ON (c.id_class = mtm.id_class);