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

0

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

id_m os_no_l os_no_r opis
1 21 23 Opis1
2 21 24 Opis2
3 20 25 Opis3
4 23 22 Opis4
5 24 22 Opis5

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

os_no imie data_urodzenia
20 Piotr 2001-01-01
21 Adam 2001-07-05
22 Ewa 2001-11-04
23 Tomek 2001-04-01
24 Paweł 2001-11-05
25 Marcin 2001-03-01
26 Ania 2001-01-01

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:
id_m os_no_l os_no_r opis
1 21 23 Opis1

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.

1

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
0

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

1

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

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

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0