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]

Faktycznie, dobry pomysł. Wyświetlam wyniki tak:

[SQL] pobierz, plaintext 
$licznik_grup = 1;
            $grupy_zapytanie = 'u2g.grupa = '0' ';
            while ($tabela_grup[$licznik_grup]) {
            $grupy_zapytanie .= ' OR u2g.grupa = ''.$licznik_grup.'' ';
            $licznik_grup++;
            }
 
            $query_n =    "    SELECT u.u_id, u.u_imie, u.u_nazwisko FROM uzytkownicy AS u
                            JOIN uzytkownicy_to_grupy u2g ON u.u_id = u2g.uzytkownik
                            JOIN grupy gr ON $grupy_zapytanie 
                            WHERE u.u_typ='nauczyciel' GROUP BY u.u_nazwisko ";
 
 
            $result_n = mysql_query($query_n) OR die("Funkcja pokaz_wszystko zawiodła... |  " . mysql_error());
                print "";
                while ($column_n = mysql_fetch_array($result_n)) {
                    print $column_n["u_imie"]. "&nbsp;" .$column_n["u_nazwisko"]. "<br> ";
                }
            $result_n = mysql_query(mysql_free_result($result_n));
[SQL] pobierz, plaintext 

To powyżej to wersja udziwniona, ale za to wreszcie działa. Z zapytania poprzedniego jest pobierana lista ID grup i potem budowane zapytanie typu "JOIN grupy gr ON $grupy_zapytanie" czyli w ciąg warunków OR. Nie jest to eleganckie ale działa.

Drugi sposób rozwiązania tego problemu to tworzenie tabeli tymczasowej, ale działa to dosyć wolno i obciąża serwer. W przypadku wykonywania kilku tysięcy takich zapytań różnica jest spora.

Sposób podany przez Ciebie jest bardzo fajny i elegancki, faktycznie zapomniałem o wyświetlaniu wyników. Może coś podrzucisz?

P.S.
Dopiero po zaktualizowaniu za serwerze MySQLa do wersji 4.1 zaczęły mi działać podzapytania. Całkiem zapomniałem że 4.0 nie ma tej funkcji...