GoldenLine
Zaloguj się

konto usunięte

Temat: relecja many-to-many

Głowię się już od dłuższej chwili.

Mam produkty i zamówienia w relacji m-to-m. Jak pobrać produkty, które nie zostały w ogóle zamówione zamówione?

struktura tabeli:
products (id, name, ..)
orders_products (product_id, order_id, quantity, ..)
orders (id, ..)
Link do wypowiedziLink

konto usunięte

Temat: relecja many-to-many

Łukasz Bandzarewicz:
Głowię się już od dłuższej chwili.

Mam produkty i zamówienia w relacji m-to-m. Jak pobrać produkty, które nie zostały w ogóle zamówione zamówione?

struktura tabeli:
products (id, name, ..)
orders_products (product_id, order_id, quantity, ..)
orders (id, ..)

select * from products where not exist (select null from orders_products where orders_products.product_id = products.id)
Link do wypowiedziLink
Marcin K.

Marcin K. Programowanie jest
moim powołaniem,
Alleluja

Temat: relecja many-to-many

mozna skorzystać online z manuala SQL(mysql, postgre) pod hasłem "SELECT","IN","ANY","ALL". można zastosować zapytanie zagnieżdżone z wykorzystaniem wspomnianych wcześniej haseł.

prosty przykład tylko trzeba przełożyć na własną bazę
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 != ALL (SELECT s1 FROM t2);
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

najprościej chyba tak, tylko pamiętaj aby klucze były indeksowane:

select name from products
where id not in ( select product_id from orders_products)
Link do wypowiedziLink

konto usunięte

Temat: relecja many-to-many

Krzysztof P.:
select * from products where not exist (select null from orders_products where orders_products.product_id = products.id)

Działa perfekcyjnie, dzięki ;)

ps.
Jaką polecacie książkę lub tutorial do SQL, gdzie można nauczyć się takich sztuczek? Nie zależy mi na podstawach, tylko na bardziej zaawansowanych zapytaniach.
Link do wypowiedziLink

konto usunięte

Temat: relecja many-to-many

Wojciech Nowak:
najprościej chyba tak, tylko pamiętaj aby klucze były indeksowane:

select name from products
where id not in ( select product_id from orders_products)

Najprosciej dla programisty ...

Tylko, że baza musi pobrać wszystkie wiersze z orders_products czyli robisz już w tym momencie full scan prawdopodobnie dość duzej tabeli. Natomiast wersja z EXISTS przerzuca full scan na tabelę z produktami, a zamówienia mogą już byc przeglądane po indeksie. No w ogóle NOT IN bardzo cięzko zmusić do korzystania z indeksu.
Link do wypowiedziLink
Wojciech Małota

Wojciech Małota Programista

Temat: relecja many-to-many

Krzysztof P.:
No w ogóle NOT IN bardzo cięzko zmusić do korzystania z indeksu.

Dlatego zamiast "id not in ()" powinno się używać "not id in ()".
Link do wypowiedziLink
Wojciech Małota

Wojciech Małota Programista

Temat: relecja many-to-many

Najprosciej dla programisty ...

Tylko, że baza musi pobrać wszystkie wiersze z orders_products czyli robisz już w tym momencie full scan prawdopodobnie dość duzej tabeli. Natomiast wersja z EXISTS przerzuca full scan na tabelę z produktami, a zamówienia mogą już byc przeglądane po indeksie.

Za to wersja z exists wykonuje zapytanie zagnieżdżone dla każdego rekordu z zapytania nadrzędnego, a wersja z "in" robi to tylko raz.
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

nie demonizowałbym tabelki orders_products. Jak dołączysz w podzapytaniu klauzulę DISTINCT po ID otrzymasz max. tyle samo rekordów co w tablicy products.
Moim zdaniem jeżeli na tych tablicach są indeksy to optymalizator powinien je wykorzystać ale trzebaby zapuścić Explain Plan.
Miałem podobne rozwiązanie na dużej ilości rekordów i nie sprawiało kłopotów.

Inna metoda to zapytanie po indeksach:

select p.id, p.name, o.product_id
from products p, orders_products o
where (+)p.id=o.product_id

a potem prosty SELECT po wyniku gdzie o.product_id jest NULL
(generalnie podobne rozwiazanie jak Twoje tylko bez funkcji exists)

Krzysztof P.:
Wojciech Nowak:
najprościej chyba tak, tylko pamiętaj aby klucze były indeksowane:

select name from products
where id not in ( select product_id from orders_products)

Najprosciej dla programisty ...

Tylko, że baza musi pobrać wszystkie wiersze z orders_products czyli robisz już w tym momencie full scan prawdopodobnie dość duzej tabeli. Natomiast wersja z EXISTS przerzuca full scan na tabelę z produktami, a zamówienia mogą już byc przeglądane po indeksie. No w ogóle NOT IN bardzo cięzko zmusić do korzystania z indeksu.
Wojciech Nowak edytował(a) ten post dnia 10.09.08 o godzinie 15:03
Link do wypowiedziLink

konto usunięte

Temat: relecja many-to-many

Wojciech Nowak:
nie demonizowałbym tabelki orders_products. Jak dołączysz w podzapytaniu klauzulę DISTINCT po ID otrzymasz max. tyle samo rekordów co w tablicy products.

Distinct powoduje sortowanie czyli dodatkowy narzut na procesory, pamięć i czasami też dyski.
Link do wypowiedziLink
Wojciech Małota

Wojciech Małota Programista

Temat: relecja many-to-many

Krzysztof P.:
Distinct powoduje sortowanie czyli dodatkowy narzut na procesory, pamięć i czasami też dyski.

Distinct powoduje, że zwrócone zostaną tylko niepowtarzalne rekordy co zaoszczędzi masę czasu przy ich porównywaniu z rekordami zwróconymi przez zapytanie nadrzędne.
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

najpierw wykona się podzapytanie z DISTINCT po indeksie id tabeli orders_product co ograniczy wynik znacznie (biorąc pod uwagę, że wielu klientów może kupić wiele razy ten sam product). Potem wyselekcjonowane id będa wstawione w warunek 'where id not in ()' - tutaj nastepny indeks tablicy products.

ms access z milionem rekordów wykona to zapytanie w kilka-kilkanascie sekund na zwykłym pc.

Krzysztof P.:
Wojciech Nowak:
nie demonizowałbym tabelki orders_products. Jak dołączysz w podzapytaniu klauzulę DISTINCT po ID otrzymasz max. tyle samo rekordów co w tablicy products.

Distinct powoduje sortowanie czyli dodatkowy narzut na procesory, pamięć i czasami też dyski.
Link do wypowiedziLink
Grzegorz G.

Grzegorz G. ASE / Systems
Architect, Syniverse

Temat: relecja many-to-many

Wojciech Nowak:
najpierw wykona się podzapytanie z DISTINCT po indeksie id tabeli orders_product

A to niby dlaczego? Znaczy niech Ci będzie, że wykona się najpierw, ale po co miałoby użyć indeksu? No, chyba, że to indeks bitmapowy...
Link do wypowiedziLink
Wojciech Małota

Wojciech Małota Programista

Temat: relecja many-to-many

Grzegorz G.:
Wojciech Nowak:
najpierw wykona się podzapytanie z DISTINCT po indeksie id tabeli orders_product

A to niby dlaczego? Znaczy niech Ci będzie, że wykona się najpierw, ale po co miałoby użyć indeksu? No, chyba, że to indeks bitmapowy...

Indeks ma to do siebie, że jest posortowany więc z jego pomocą szybciej się znajduje unikalne wartości.

Pozdrawiam,
Wojtek
Link do wypowiedziLink

konto usunięte

Temat: relecja many-to-many

Wojciech Małota:
Krzysztof P.:
Distinct powoduje sortowanie czyli dodatkowy narzut na procesory, pamięć i czasami też dyski.

Distinct powoduje, że zwrócone zostaną tylko niepowtarzalne rekordy co zaoszczędzi masę czasu przy ich porównywaniu z rekordami zwróconymi przez zapytanie nadrzędne.

DISTINCT przede wszystkim oszczędza ruch sieciowy.
Co do CPU, dysków i pamięci to rzecz dyskusyjna (zależna od engina, instancji, bazy...)

Generalnie DISTINCT jest BARDZO obciążający i stosuje się go głównie do raportów. No ale ludzie różne skrypty piszą...
Link do wypowiedziLink
Wojciech Małota

Wojciech Małota Programista

Temat: relecja many-to-many

Piotr Likus:
DISTINCT przede wszystkim oszczędza ruch sieciowy.
Co do CPU, dysków i pamięci to rzecz dyskusyjna (zależna od engina, instancji, bazy...)

Generalnie DISTINCT jest BARDZO obciążający i stosuje się go głównie do raportów. No ale ludzie różne skrypty piszą...

Poczytaj manuala wybranego DBMS w kwestii optymalizacji zapytań zagnieżdżonych bo to co piszesz jets w totalnej sprzeczności z tym co się tam opisuje.
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

zgadzam się z Tobą.

poza tym, co to znaczy że DISTINCT stosuje sie do raportów. Stosuje sie kiedy jest potrzeba. Podstawą optymalizacji jest EXPLAIN PLAN wiec jesli DISTINCT sprawi ze zapytanie bedzie sie wykonywac efektywniej to w czym problem.
Równie dobrze można powiedzieć , że COUNT lub ORDER BY obciąza bardzo serwer.
Wojciech Małota:
Poczytaj manuala wybranego DBMS w kwestii optymalizacji zapytań zagnieżdżonych bo to co piszesz jets w totalnej sprzeczności z tym co się tam opisuje.
Wojciech Nowak edytował(a) ten post dnia 10.09.08 o godzinie 20:42
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

no pewnie, że wykona sie najpierw. Przeciez aby podstawic do warunku 'where not in ()' poszczegolne numery ID to musi je najpierw znaleźć podzapytaniem.

Co do indeksu w podzapytaniu to moim zdaniem powinien skorzystac. Łatwo to zasymulować i sprawdzić explain plan.
Grzegorz G.:
Wojciech Nowak:
najpierw wykona się podzapytanie z DISTINCT po indeksie id tabeli orders_product

A to niby dlaczego? Znaczy niech Ci będzie, że wykona się najpierw, ale po co miałoby użyć indeksu? No, chyba, że to indeks bitmapowy...
Link do wypowiedziLink
Wojciech Nowak

Wojciech Nowak Konsultant Systemów
Bankowych | Analityk
Systemowy

Temat: relecja many-to-many

nie do końca się z tym zgadzam...
DISTINCT chociażby bardzo sie przydaje do usuwania redundancji.
Piotr Likus:
Generalnie DISTINCT (...) stosuje się go głównie do raportów. No ale ludzie różne skrypty piszą...
Wojciech Nowak edytował(a) ten post dnia 10.09.08 o godzinie 20:57
Link do wypowiedziLink
Jerzy Siwek

Jerzy Siwek Analyst Billing
Software Systems

Temat: relecja many-to-many

nie wiem czy juz byla ta propozycja:

select a.*
from products a
left join order_products b on a.id = b.product_id
where b.product_d is nullJerzy Siwek edytował(a) ten post dnia 11.09.08 o godzinie 09:06
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj