Ciekawe (i trudne) zapytanie SQL - POMOCY!, ...czyli problemy z relacjami wiele do wielu Opcje

[vilq]

Przyjaciele!

Przedstawiam poniżej problem nad którym ostatnio pracuję. Na początku wydawało się że to klasyczny przykład relacji. Niestety nie jest to tak oczywiste jak się na początku wydawało. Może ktoś pomoże...?

Mam bazy danych: uzytkownicy, uzytkownicy_2_grupy, grupy - modelowo.
Użytkownicy:

Kod

u_id | u_imie | u_nazwisko | u_typ       |
                ==========================================
              | 1  | Adam   | Nowak      | kursant     |
              | 2  | Jan    | Kowalski   | kursant     |
              | 3  | Adam   | Roch       | nauczyciel  |
              | 4  | Piotr  | Gebalski   | kursant     |
              | 5  | Michał | Ciaptak    | nauczyciel  |
              | 6  | Ewa    | Dębska     | nauczyciel  |
              | 7  | Marta  | Marczak    | kursant     |
                    itd...

Użytkownicy_2_grupy:

Kod

           id | uzytkownik | grupa
        ===============================
        | 1  |    1       |     1     |
      | 2  |    1       |     2     |
      | 3  |    3       |     2     |
      | 4  |    4       |     2     |
      | 5  |    4       |     1     |
      | 6  |    5       |     2     |
      | 7  |    6       |     1     |
      | 8  |    7       |     1     |
      itd...

grupy:

Kod

          
            gr_id | gr_nazwa | gr_cośtam | itd...    |
                ==========================================
              | 1   | grupa1   | opis      |           |
              | 2   | grupa2   | opis      |           |
            
                    itd...

I teraz zaczyna się dopiero jazda.
Zadanie jest takie: w raporcie o wybranym (pojedynczym) kursancie należy:
1. wylistować wszystkie grupy do jakich uczęszcza (a może do wielu)
i tutaj odpowiedź jest prosta:

CODE

$zapytanie_grupy = " SELECT gr.gr_id, gr.gr_nazwa, gr.gr_poziom, u2g.*, u.*
FROM `grupy` AS gr, `uzytkownicy_2_grupy` AS u2g, `uzytkownicy` AS u
WHERE gr.gr_id = u2g.grupa AND u2g.uzytkownik = '$id_uzytkownika' AND u.u_id = '$id_uzytkownika' ";

gdzie '$id_uzytkownika' jest oczywiście jakimś konkretnym ID.

2. - I TUTAJ powstaje problem: wylistować wszystkich nauczycieli, jacy uczą kursanta

Na razie udaje mi się wylistować wszystkich nauczycieli, którzy mają grupy (są przydzieleni do grup):

CODE

SELECT u.* FROM `uzytkownicy` AS u
JOIN `uzytkownicy_to_grupy` AS u2g ON (u.u_id = u2g.uzytkownik)
JOIN `grupy` AS gr ON (u2g.grupa = gr.gr_id)
WHERE u.u_typ = 'nauczyciel' AND
ORDER BY u.u_nazwisko

Ale nie można w zapytaniu podać jednoznacznego ID użytkownika ani ID grupy, ponieważ:
1. grupa może mieć kilku nauczycieli
2. kursant może być w kilku grupach
3. wybieramy nauczycieli z tej samej tabeli w której są kursanci (różni ich tylko pole u_typ)

Niby proste, ale nie mogę tego przejść. Niestety nie wchodzi w grę przebudowa tabel.

Czy ktoś może wie, jak można sprawę załatwić jednym eleganckim poleceniem a nie rekurencją poleceń MySQL?

Pozdrawiam

Ten post edytował vilq 19.09.2008, 19:29:54

[vilq]

... a więc sprawa wygląda tak:

1. po zastosowaniu całości w postaci (u_typ = nauczyciel a potem u.u_typ = kursant):

[SQL] pobierz, plaintext 
$query_n =    "    SELECT u.u_id, u.u_imie, u.u_nazwisko, GROUP_CONCAT(DISTINCT gr.gr_nazwa ORDER BY gr.gr_nazwa), GROUP_CONCAT(DISTINCT un.u_id ORDER BY un.u_id) FROM uzytkownicy u
                            LEFT JOIN uzytkownicy_to_grupy u2g ON u.u_id = u2g.uzytkownik
                            LEFT JOIN grupy gr ON gr.gr_id = u2g.grupa
                            LEFT JOIN (SELECT * FROM uzytkownicy LEFT JOIN uzytkownicy_to_grupy on uzytkownicy.u_id = uzytkownicy_to_grupy.uzytkownik
                            WHERE u_typ = 'nauczyciel') un ON un.grupa = u2g.grupa
                            WHERE u.u_typ='kursant' GROUP BY u.u_nazwisko ";
[SQL] pobierz, plaintext 

Otrzymuję wszystkich kursantów POZA nauczycielami, niezależnie, czy w ogóle są w jakiejś grupie.

2. Po zastosowaniu zmiany "...WHERE u_typ = nauczyciel [...] u.u_typ = kursant"
otrzymuję wszystkich nauczycieli razem z tymi bez przydzielonej żadnej grupy

3. Po zastosowaniu kombinacji "...WHERE u_typ = kursant [...] u.u_typ = nauczyciel"
dokładnie to samo co w punkcie 2.

4. Po zastosowaniu kombinacji "...WHERE u_typ = kursant [...] u.u_typ = kursant"
dokładnie to samo co w punkcie 1.


Kolejna próba to zatem było przypisanie do podzapytania konkretnej wartości $id_uzytkownika:

[SQL] pobierz, plaintext 
$query_n =    "    SELECT u.u_id, u.u_imie, u.u_nazwisko, GROUP_CONCAT(DISTINCT gr.gr_nazwa ORDER BY gr.gr_nazwa), GROUP_CONCAT(DISTINCT un.u_id ORDER BY un.u_id) FROM uzytkownicy u
                            LEFT JOIN uzytkownicy_to_grupy u2g ON u.u_id = u2g.uzytkownik
                            LEFT JOIN grupy gr ON gr.gr_id = u2g.grupa
                            LEFT JOIN (SELECT * FROM uzytkownicy LEFT JOIN uzytkownicy_to_grupy on uzytkownicy.u_id = uzytkownicy_to_grupy.uzytkownik
                            WHERE u_id = '$id_uzytkownika') un ON un.grupa = u2g.grupa
                            WHERE u.u_typ='nauczyciel' GROUP BY u.u_nazwisko ";
[SQL] pobierz, plaintext 

i wtedy dokładnie to samo.

Wniosek: wszystko to, co jest w podzapytaniu nic nie daje i nie ma wpływu na wynik.

Przy okazji wpadł mi pomysł: funkcja listingu wszystkich grup do jakich kursant uczęszcza działa OK i jest przetestowana. Może więc użyć tych wyników do konstrukcji tabeli tymczasowej? W wyniku takiego zapytania mamy kolekcję ID grup do których jest zapisany kursant. Czy to coś daje?

Pozdr

Ten post edytował vilq 19.09.2008, 19:57:51