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

[kitol]

strzelam:

[SQL] pobierz, plaintext 
SELECT u.u_nazwa, GROUP_CONCAT(DISTINCT g.gr_nazwa ORDER BY g.gr_nazwa), GROUP_CONCAT(DISTINCT un.u_nazwa ORDER BY un.u_nazwa) FROM uzytkownicy u
LEFT JOIN uzytkownicy2grupy u2g ON u.u_id=u2g.uzytkownik
LEFT JOIN grupy g ON g.id=u2g.grupa
LEFT JOIN (SELECT * FROM uzytkownicy LEFT JOIN uzytkownicy2grupy ON uzytkownicy.u_id=uzytkownicy2grupy.uzytkownik
           WHERE u_typ='nauczyciel') un ON un.grupa=u2g.grupa
WHERE u.u_typ='kursant' GROUP BY u.u_nazwa
[SQL] pobierz, plaintext 

wydaje mi się że powinno działać. Daj znać

[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

[kitol]

Pokaż jak wyświetlasz wynik zapytania, bo wydaje mi się że coś źle robisz. Najlepiej spróbuj wykonać to zapytanie w phpMyAdminie i zobacz co jest zwracane.
Ja otrzymuję wynik:

CODE

adam nowak | grupa1,grupa2 | adam roch,ewa debska,michal ciaptak
jan kowalski | |
marta marczak | grupa1 | ewa debska
piotr gebalski | grupa1,grupa2 | adam roch,ewa debska,michal ciaptak

W pierwszej kolumnie jest kursant
w drugiej, do jakich grup uczęszcza,
w trzeciej, jacy nauczyciele uczą w tych grupach

[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...

[zooocha1988]

Witam, mam problem z kilkoma zapytaniami sql prosze o pomoc
zad1
1)napisz polecenie sql tworzac tablice miejscowosci, zakladajac ze tabela woj juz istenieje w bazie
2) napisz polecenie sql zwracajace wszystkie nazwy województw wraz z nazwa grupy wiekowej, ktora w danym wojew najlczniej planuje brac udzial w wyborach. w przypadku gdy w danym wojew nikt nie zamierza brac udzialu w wyborach powinna byc zwrocona tylko nazwa wojewodztwa bez zadnej grupy wiekowej

dodatkowo mamy tabele :
wojewodztwo w niej (id_wojewodztwa, nazwa)
miejscowosc (od_miejscowosc, id_wojewodztwa, nazwa, l_mieszkancow)
ankieta(id_ankiety, id_miejscowosc, wiek ,planuje_udzial)
typ_miejscowosci(id_typ_miejscowosci, nazwa, od_liczby_mieszkancow, do_liczby_mieszkancow)
grupa_wiekowa(id_grupy, nazwa, od_lat, do_lat)

oraz zad 2
dana jest baza danych z tabelami:
dzial(id_dzial numeric (8,0), nazwisko_imie varchar(100)), pensja numeric(8,2))
napisz polecenie sql ktore zwroci liste wsyzstkich pracownikow z dzialu ktory zatrudnia najwieksza liczbe pracownikow. jezeli beda istanialy dwa lub wiecej takich dzialow to polecenie ma zwrocic pracownikow wszystkich tych dzialow

[piotrooo89]

proszę nie odgrzebywać starego tematu tylko założyć nowy, zamykam.