[PL/SQL] Problem ze złożonym zapytaniem

Witam,

W codziennej pracy z bazą danych Oracle, napotkałem taki problem który postaram się opisać schematycznie.

Mam np. tabelę spotkań która zawiera dwie osoby uczestniczące + opis (taki przykład skrócony)
Tabela wygląda tak:

T_SPOTKANIA

Pola os_no_l i os_no_r są kluczami obcymi tabeli osób - która zawiera wiele innych informacji (w skróconej wersji wygląda tak jak poniżej).

T_OSOBA

Potrzebuje zrobić zapytanie, które będzie w wyniku dawało mi informację o spotkaniach (imie obu osób + opis - ale tylko dla spotkań gdy osoba z kolumny os_no_l jest starsza od osoby os_no_r
(taki przypadek czysto schematyczny, pozbawiony większego merytorycznego sensu - ale obrazowy)

Dołączyłem dwukrotnie tabelę osób i napisałem zapytanie które działa bez zarzutów (teraz piszę go z palca, przepraszam za literówki jeśli takowe wystąpią)

SELECT 
s.id_m,
l.imie,
r.imie,
s.opis

FROM T_SPOTKANIA s, T_OSOBA l, T_OSOBA r

WHERE s.os_no_l  = l.os_no
and s.os_no_r =  r.os_no
and l.data_urodzenia < r.data_urodzenia

Przypadek skomplikował się, gdyż w innej bazie tabela osób jest bardziej rozbudowana i zawiera m.in polę update_os_no które jest także kluczem do tabeli osób, i wskazuje
rekord poprawny ( zaktualizowany).

Tak wygląda przykładowa tabela:

os_no | imie | data_urodzenia | update_os_no
---------------- | -------------------| -------------------|
20 | Piotr| 2001-01-01 | null
21| Adam| 2001-07-05 | null
22 | Ewa| 2001-11-04 | null
23| Tomek| 2001-04-01 | 27
24 | Paweł| 2001-11-05 | null
25 | Marcin| 2001-03-01 | 28
26 | Ania| 2001-01-01 | null
27 | Tomek| 2001-04-06 | 27
28 | Marcin| 2006-03-01 | 28

Nie mogę sobie za nic w świecie poradzić, jak napisać moje zapytanie aby teraz działało poprawnie..
Logika jest taka jak w pierwszym przypadku, z tym że trzeba wykonać dodatkowe sprawdzenie, czy osoba po prawej stronie uczestnicząca w spotkaniu ma aktualizowany rekord, (czy ma wpisane jakieś ID w tym polu) - (osoba po lewej stronie nas nie obchodzi).

Np.

• dla spotkania:

Chcemy wyświetlić imie osoby z ID_21 = ADAM, imię osoby z ID_23 = TOMEK i podać daty ich urodzin, a także sprawdzić
warunek: and l.data_urodzenia < r.data_urodzenia - z tym że osoba z ID = 23 ma wartość w polu update_os_no = 27 co oznacza że poprawna wartość daty dla tej osoby jest w tym rekordzie (została zaktualizowana w późniejszym terminie i zapisała się jako osobny rekord).

Podsumowując, potrzebuje zmodyfikować moje zapytanie tak aby dla osoby z prawej strony (os_no_r) zawsze wykonane było sprawdzenie czy polę update_os_no jest puste - jeśli nie jest puste to po tym polu trzeba dojść do właściwego rekordu z tabeli osoby i z niego pobrać datę.

Próbowałem wprowadzać podzapytania, kolejne złączenie.. ale nie mogę sobie poradzić.
Wielka prośba o pomoc.
Wyświetlić właściwą datę potrafię (wprowadzając trzecie dołączenie tabeli osoby), jednak nie mogę jej wykorzystać już w klauzuli WHERE..

Będe bardzo wdzięczny za pomoc
Pozdrawiam
P.

Masz dwie możliwości: albo łączyć się po trzech polach z tabelą osoby, w przypadku kolumny update_os_no left joinem i budować warunek where:

SELECT 
s.id_m,
l.imie,
r.imie,
s.opis
FROM 
    T_SPOTKANIA s
    inner join  T_OSOBA l on s.os_no_l  = l.os_no
    inner join  T_OSOBA r on  s.os_no_r =  r.os_no
    left join  T_OSOBA u on  s.	update_os_no =  u.os_no
WHERE 
    (l.data_urodzenia < r.data_urodzenia AND s.update_os_no is null)
    or 
   (l.data_urodzenia < u.data_urodzenia AND not s.update_os_no is null)

Jeżeli natomiast t_osoba zawiera pełny zaktualizowany rekord osoby tp możesz pobrać rekordy samym złączeniem:

SELECT 
s.id_m,
l.imie,
r.imie,
s.opis
FROM 
    T_SPOTKANIA s
    inner join  T_OSOBA l on s.os_no_l  = l.os_no
    inner join  T_OSOBA r on  COALESCE (s.update_os_no, s.os_no_r) =  r.os_no
WHERE 
    l.data_urodzenia < r.data_urodzenia

Jeżeli natomiast t_osoba zawiera pełny zaktualizowany rekord osoby tp możesz pobrać rekordy samym złączeniem:

SELECT 
s.id_m,
l.imie,
r.imie,
s.opis
FROM 
    T_SPOTKANIA s
    inner join  T_OSOBA l on s.os_no_l  = l.os_no
    inner join  T_OSOBA r on  COALESCE (s.update_os_no, s.os_no_r) =  r.os_no
WHERE 
    l.data_urodzenia < r.data_urodzenia

• tak zawiera pełen zaktualizowany rekord, to drugie zapytanie będzie właściwe.

Nigdy nie używałem wcześniej złączenia połączonego z COALESCE., jestem delikatnie mówiąc w pozytywnym szoku :)
Dobrze rozumiem analizując kod?

• "lewa" osoba zostanie zwykłym złączeniem pobrana z tabeli T_OSOBA (bo dla niej nic nie sprawdzamy)
• "prawa" osoba zostanie złączona z funkcją COALESCE - kolejność w nawiasie oznacza, że najpierw do złączenia użyte będzie pole s.update_os_no, a w przypadku kiedy będzie ono miało wartość null, zostanie użyte s.os_no_r? ??

To by rzeczywiście załatwiało sprawę..

Tak, wyrażenie COALESCE (s.update_os_no, s.os_no_r) zwróci s.os_no_r jeżeli update_os_no będzie miało wartość null. Dla opisywanego przypadku join dla "nullowych" update_os_no połączy się po polu os_no_r.

Warto pamiętać że warunek złączenia nie nusi zawierać tylko równości ;)

Panczo napisał(a):

Tak, wyrażenie COALESCE (s.update_os_no, s.os_no_r) zwróci s.os_no_r jeżeli update_os_no będzie miało wartość null. Dla opisywanego przypadku join dla "nullowych" update_os_no połączy się po polu os_no_r.

Warto pamiętać że warunek złączenia nie nusi zawierać tylko równości ;)

jesteś wielki :) nie wiedziałem... to rozwiązuje wiele problemów z jakimi spotykałem się ostatnio :) Ogromne dzięki.