All geek questions in one place

Strange relationships of many and from one to many

I know that I will have voices, but I have to make sure that it is logical or not.

I have three tables A, B, C. B is the table used to establish the many-many relationship between A and C. But the fact is that A and C are also directly related to the 1-many relationship

The client added the following requirement:

Get the information from the inner insert of table B with A and C, and in the same query, link A and C to the one-to-many relationship

Sort of:

alt text http://img247.imageshack.us/img247/7371/74492374sa4.png

I tried to execute the query, but always got 0 rows back. The client insists that I can fulfill this requirement, but I doubt it. Any comments?

PS. I did not have a more descriptive name, any ideas?

UPDATE: Thanks to rcar, in some cases it might be logical to have a history of all the classes a student took (suppose a student can only take one class at a time)

UPDATE: There is a table for contacts, a table with the information of each contact and a relationship table. To get information about a contact, I have to establish a 1: 1 connection with the information, and each contact can have an address book; therefore, many, many relationships are realized.

The complete idea is to get the name of the contact and its address book. Now that I got the idea of ​​the client ... I'm having problems with the request, basically I try to use the request that jdecuyper wrote, but as he warns, I am not getting any data back

+3
source share
6 answers

, s.id_class , , .

, rcar, , c1.className .

, , . s.id_class c.id_class, mtm.

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);

is_current 1 (true) 0 (false) . - , CASE:

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);
+2

. , , .

:

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

" " many_to_many.

, . , , , , many_to_many , , . id_class, s, many_to_many (, s.id_class , , homeroom, , many_to_many.id_class homeroom) c .

, , , s.

: , , . -. , , , "" "". "1-" , - , "--" "", , .

, , , .

+5

, , . , , , , . , , , student class. ..

  • , 1245235
  • , 101

? , . (A C), , B, ...

+3

, " " --, , , . "" "".

" , ? , , : " " ( , -, ) " " . , , !

+3

. :

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

can generate a data set, but incompatible. Or maybe we are missing an important piece of information about the content and relationships of these three tables.

+1
source

I personally have never heard a demand from a client that would read as follows:

Get information from table B connecting to A and C, and in the same query links A and C into a one-to-many relationship

Looks like this is what you translated this requirement to. Could you indicate the requirement in plain English, like what your client wants to receive?

0
source

Source: https://habr.com/ru/post/1698622/

More articles:


All Articles