[MySQL] Połączenie dwóch tabel i wyodrębnienie danych Opcje

[zgRED]

Witam,

Mam problem z wyodrębnieniem danych na podstawie zapytania łączącego dwie tabele. W uproszczeniu wyglądają one następująco:

Tabela produkty:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `produkty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(255) CHARACTER SET latin2 NOT NULL DEFAULT 'inactive',
  `koszt` decimal(10,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`id`)) 
[SQL] pobierz, plaintext 

Tabela faktury:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `faktury` (
  `ad` int(255) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `id` int(10) NOT NULL DEFAULT '0',
  `kwota` decimal(10,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`ad`)) 
[SQL] pobierz, plaintext 

Chciałbym w efekcie uzyskać taki efekt: dane będą pobierane dla danego użytkownika z np. user_id=2. Wyświetlać mają się więc wszyskie produkty, poza produktami dodanym przez użytkownika sprawdzającego (WHERE produkty.user_id NOT LIKE '2'), o koszcie większym od 10000 (AND produkty.koszt>10000), o statusie aktywnym (AND produkty.status='active') - lecz tylko te produkty dla których sprawdzający użytkownik nie wystawił jeszcze faktur (wyświetlić tylko te rekordy dla których nie istnieją wpisy w tabeli faktury odpowiadające danym produktom (faktury.id) i użytkownikowi sprawdzającemu (faktury.user_id)).

Problem mój polega na tym, iż nie potrafię zdefiniować ostatniej część zapytania. Tabele złączyłem poprzez LEFT OUTER JOIN faktury ON produkty.id=faktury.id Nie potrafię jednak trafnie wykorzystać np. składni WHERE NOT EXISTS aby wyfiltrować produkty bez faktur. Liczyłbym na Waszą pomoc jeśli uda Wam się zrozumieć co mam w zamyśle - choć starałem się opisać to dość precyzyjnie

Pozdrawiam.

[aniolekx]

Tabele produkty nazwał bym: sprzedane_produkty, poza tym nie zalecam używania polskich nazw.

[SQL] pobierz, plaintext 
WHERE produkty.user_id NOT LIKE '2
[SQL] pobierz, plaintext 

powinno byc:

[SQL] pobierz, plaintext 
WHERE produkty.user_id != '2'
[SQL] pobierz, plaintext 

Brakujący warunek:

[SQL] pobierz, plaintext 
faktury.ad IS NULL
[SQL] pobierz, plaintext 

Ten post edytował aniolekx 6.10.2013, 08:14:00

[zgRED]

No niestety nie daje to do końca efektu jaki chciałbym uzyskać. Kod:

[SQL] pobierz, plaintext 
faktury.ad IS NULL
[SQL] pobierz, plaintext 

pozwala co prawda na wybranie takich produktów dla których nie ma odpowiadających im wpisom w tabeli faktury. Natomiast nie uwzględnia to sytuacji, gdy dla danego produktu (produkty.id) wpis w tabli faktury istnieje, jednak jest on przypisany innemu użytkownikowi niż użytkownik sprawdzany (faktury.user_id). Tutaj nie mam pomysłów jak to uwzględnić. Efekt jaki chciałbym uzyskać postaram się opisać bardziej precyzyjnie.

Tabela produkty uzupełniona będzie w ten sposób:

id user_id status koszt
1 2 active 15000
2 2 active 12000
3 5 active 11000
4 6 active 16000
5 6 active 12000
6 3 active 11000

Tabela faktury uzupełniona będzie w ten sposób:

ad user_id id kwota
1 2 3 11000
2 2 5 12000
3 6 1 15000

Przykład 1. Gdy sprawdza dane uzytkownik nr 2 (user_id=2) to ma w efekcie wyświetlić mają się następujące produkty:

id user_id status koszt
4 6 active 16000
6 3 active 11000

Nie wyświetlane są więc produkty 1,2,3 ponieważ zostały dodane przez użytkownika nr 2 (WHERE produkty.user_id != '2'). Nie wyświetlają się produkty 3,5 ponieważ już użytkownik dodał do nich fakturę i wpis taki istnieje w tabeli faktury. Wyświetlają się więc tylko produkty 4 i 6 których nie dodał użytkownik nr 2 i które nie znajdują się w tabeli faktury z jego user_id=2.

Przykład 2. Gdy sprawdza dane uzytkownik nr 6 (user_id=6) to ma w efekcie wyświetlić mają się następujące produkty:

id user_id status koszt
2 2 active 12000
3 5 active 11000
6 3 active 11000

Nie wyświetlane są więc produkty 4,5 ponieważ zostały dodane przez użytkownika nr 6 (WHERE produkty.user_id != '6'). Nie wyświetla się produkt 1 ponieważ już użytkownik dodał do niego fakturę i wpis taki istnieje w tabeli faktury. Wyświetlają się więc tylko produkty 2,3,6 których nie dodał użytkownik nr 6 i które nie znajdują się w tabeli faktury z jego user_id=6.

Ten problem męczy mnie już od kilku dni, pewnie istnieje jakieś banalne rozwiązanie ale ja zupełnie nie wiem jak to ugryźć. Może ktoś wpadnie na pomysł. User_id użytkownika który dokonuje sprawdzenia jest dostarczane przez $_GET[user_id]

Ten post edytował zgRED 6.10.2013, 12:57:40

[webmaniak]

No niestety nie daje to do końca efektu jaki chciałbym uzyskać. Kod:

[SQL] pobierz, plaintext 
faktury.ad IS NULL
[SQL] pobierz, plaintext 

pozwala co prawda na wybranie takich produktów dla których nie ma odpowiadających im wpisom w tabeli faktury. Natomiast nie uwzględnia to sytuacji, gdy dla danego produktu (produkty.id) wpis w tabli faktury istnieje, jednak jest on przypisany innemu użytkownikowi niż użytkownik sprawdzany (faktury.user_id). Tutaj nie mam pomysłów jak to uwzględnić $_GET[user_id]

[SQL] pobierz, plaintext 
[...]WHERE `faktury.ad` IS NULL OR (`faktury.ad` IS NOT NULL AND `produkty.user_id` != '2')
[SQL] pobierz, plaintext 

albo:

[SQL] pobierz, plaintext 
[...]`faktury.user_id` != '2'
[SQL] pobierz, plaintext 

Tylko tej drugiej wersji nie jestem pewny czy zadziała tak jak tego oczekujesz.

[zgRED]

Nie, niestety to jeszcze nie to

Niestety dla przykładowych rekordów w tabelach problemu nie widać.

Dla sprawdzającego użytkownika z np. user_id=2, gdy w tabeli faktury istnieje wpis odpowiadający danemu 'produkty.id' z tabeli produkty, nawet gdy faktura nie była wystawiona na użytkownika sprawdzające (faktury.user_id!='2'), lecz została wystawiona dla tego samego produktu przez użytkownika z np. user_id='6' to niestety taki wpis też jest wyświetlany. Dodając jeden rekord do wcześniej przedstawionej tabeli faktury:

Tabela produkty uzupełniona będzie w ten sposób:

id user_id status koszt
1 2 active 15000
2 2 active 12000
3 5 active 11000
4 6 active 16000
5 6 active 12000
6 3 active 11000

Tabela faktury uzupełniona będzie w ten sposób:

ad user_id id kwota
1 2 3 11000
2 2 5 12000
3 6 1 15000
4 6 3 11000

Gdy sprawdza dane użytkownik nr 2 (user_id=2), wyświetlają się mu następujące produkty:

id user_id status koszt
3 5 active 11000
4 6 active 16000
6 3 active 11000

Zamiast:

id user_id status koszt
4 6 active 16000
6 3 active 11000

Nadprogramowo więc wyświetla się produkt z id=3, dla którego istnieje wpis w tabeli faktury, lecz przypisany jest do użytkownika z user_id=6 (niestety oba warunki: WHERE `faktury.ad` IS NULL OR (`faktury.ad` IS NOT NULL AND `produkty.user_id` != '2') są spełnione). Może jest jakieś inne rozwiązanie tego problemu od innej strony?

To jakiś koszmar...

[webmaniak]

Czyli są warunki:
1) faktura.ad może być null
2) faktura.ad nie jest null ale wtedy id usera nie może być takie samo co id sprawdzającego
3) jeśli produkt został wcześniej dodany to ma nie być wyświetlany
Dobrze rozumiem?

[zgRED]

Czyli są warunki:
1) faktura.ad może być null
2) faktura.ad nie jest null ale wtedy id usera nie może być takie samo co id sprawdzającego
3) jeśli produkt został wcześniej dodany to ma nie być wyświetlany
Dobrze rozumiem?

Odnośnie warunków

1) faktura.ad może być null
2) faktura.ad nie jest null ale wtedy id usera nie może być takie samo co id sprawdzającego
3) jeśli FAKTURA został wcześniej dodana PRZEZ USERA Z ID SPRAWDZAJĄCEGO to ma nie być wyświetlana
4) uzupełnienie do 3): jeśli FAKTURA (przypisana do jednego produktu), została wcześniej dodana PRZEZ USERA Z ID SPRAWDZAJĄCEGO i została dodana też przez USERA z innym ID (niestety np. warunek produkty.user_id` != '2' jest spełniony) również taki produkt nie powinien się wyświetlać

Obecnie nie mam pomysłu na warunek nr 4. To jest moim zdaniem problem nie do rozwiązania poprzez obecne zapytanie MySQL, z LEFT OUTER JOIN. Męczy mnie to strasznie.

[webmaniak]

Widze na to dwa sposoby:
1) przy tworzeniu faktury sprawdzasz czy faktura została wcześniej dodana wg tego co pisałeś w warunkach 3 i 4 - jeśli tak, to w utworzonej kolumnie, np. duplicated dajesz 1/true. Potem już z górki, sprawdzasz czy ta faktura ma true czy false w kolumnie duplicated
2) tworzysz dwa zapytania: w pierwszym sprawdzasz warunki 3 i 4, w drugim do warunku 1 i 2 dodajesz że nie mogą to być id z pierwszego zapytania.
W taki sposób ja bym to zrobił(w podobnym module tak robiłem tylko tam dawałem takie zapytanie w nieco innym celu).

[zgRED]

Dziękuję za pomoc i zainteresowanie, ostatecznie uporałem się stosując dwa zapytania aczkolwiek już nie łączę dwóch tabel, zapytania uprościłem.

pozdrawiam