Znajdowanie rekordów z pojedynczą relacją w innej tabeli, Join + On + Group By + Having Opcje

[Grzeniu]

Cześć,

Mam obecnie taką strukturę tabeli:
tabela vtiger_activity - gdzie są wpisy dotyczące zdarzeń mające m.in. swoje id - vtiger_activity.activityid oraz status - vtiger_activity.eventstatus
tabela vtiger_crmentityrel - gdzie zapisane sa relacje między różnymi typami rekordów, w tym przypadku między ID klientów (crmid) oraz ID zdarzeń (relcrmid), które są przypisane do danych klientów. Pojedynczy klient może mieć przypisanych wiele zdarzeń.
Tutaj bardziej obrazowo:


Ja chciałbym wybrać z tabeli vtiger_activity tylko te rekordy zdarzeń, które są jedynymi przypisanymi do swoich klientów. Przykładowo: klient A ma zdarzenia Z1, Z2, Z3, klient B ma tylko Z4, klient C ma tylko Z5. Czyli ja chcę wybrać tylko Z4, Z5.

Próbowałem wykonać to za pomocą group by i having count(*) = 1, jak poniżej, lecz to nadal zwraca takie zdarzenia, które przypisane są do klientów z kilkoma przypisanymi zdarzeniami. Nie potrafię obmyślić prawidłowej logiki tego zapytania, więc sugestie bardzo mile widziane.

[SQL] pobierz, plaintext 
SELECT 
	vtiger_crmentityrel.crmid AS id_klienta,
	vtiger_crmentityrel.relcrmid AS id_zdarzenia,
	vtiger_activity.subject,
	vtiger_activity.eventstatus 
FROM vtiger_activity
JOIN panel.vtiger_crmentityrel 
ON vtiger_crmentityrel.relcrmid = vtiger_activity.activityid
GROUP BY vtiger_crmentityrel.crmid
	HAVING count(*) = 1
[SQL] pobierz, plaintext 

Ten post edytował Grzeniu 26.10.2017, 11:02:03

[sabat24]

Nie mam teraz chwili, by przekładać Twoje nazwy kolumn, ale ja używam poniższej mechaniki, by osiągnąć to, co Ty chcesz.

Do głównego SELECTa (pomiń warunki i JOINy związane z tym, że liczba powiązań musi być równa 1) dodaj coś takiego:

[SQL] pobierz, plaintext 
WHERE EXISTS (
SELECT a2r.room_id FROM accessories_to_rooms a2r WHERE a2r.room_id = r.r_id AND a2r.company_id = c.c_id GROUP BY a2r.room_id HAVING COUNT(a2r.room_id) = 1)
[SQL] pobierz, plaintext 

U mnie a2r to jest tabela z relacjami n:n (u Ciebie zdarzenia) a r to jest ta Twoja kliencka.
To zwraca tylko te rekordy z tabeli klienckiej, które mają dokładnie X relacji.

[Grzeniu]

[SQL] pobierz, plaintext 
WHERE EXISTS (
SELECT a2r.room_id FROM accessories_to_rooms a2r WHERE a2r.room_id = r.r_id AND a2r.company_id = c.c_id GROUP BY a2r.room_id HAVING COUNT(a2r.room_id) = 1)
[SQL] pobierz, plaintext 

U mnie a2r to jest tabela z relacjami n:n (u Ciebie zdarzenia) a r to jest ta Twoja kliencka.
To zwraca tylko te rekordy z tabeli klienckiej, które mają dokładnie X relacji.

Starałem się poukładać swoje zapytanie, ale coś ewidentnie robię źle. Puszczam zapytanie przez phpAdmina i wykonuje się ono chyba w nieskończoność - aż serwer zrywa połączenie.

Poniżej kod. W twoim poście nie rozumiałem
FROM accessories_to_rooms a2r WHERE
Czym jest w moim przypadku twoje "accessories_to_rooms"?

[SQL] pobierz, plaintext 
SELECT 
	vtiger_crmentityrel.crmid AS id_klienta,
	vtiger_crmentityrel.relcrmid AS id_zdarzenia,
	vtiger_activity.subject,
	vtiger_activity.eventstatus 
FROM vtiger_activity
JOIN vtiger_crmentityrel 
WHERE EXISTS (
	SELECT vtiger_crmentityrel.relcrmid
	FROM vtiger_crmentityrel
	WHERE vtiger_crmentityrel.relcrmid = vtiger_activity.activityid
	GROUP BY vtiger_crmentityrel.relcrmid
	HAVING COUNT(vtiger_crmentityrel.relcrmid) = 1
)
[SQL] pobierz, plaintext 

Ten post edytował Grzeniu 30.10.2017, 11:36:25

[trueblue]

A jaki wynik daje oryginalne zapytanie bez łączenia z tabelą vtiger_activity?

[sabat24]

Ja nie używam JOINa do pobierania wyników, więc u mnie go nie ma. Możliwe, że tu jest problem. Pisałem Ci PW. Prześlij mi kawałek swoich tabel z danymi, to zerknę na to u siebie.

Edit:

FROM accessories_to_rooms a2r WHERE
Czym jest w moim przypadku twoje "accessories_to_rooms"?

U mnie:
rooms to są Twoi klienci, gdzie r_id jest ich kluczem primary - Twój crmid
accessories to u mnie Twoje relacje, czyli Z5, Z4 - Twój klucz to relcrmid
accessories_to_rooms to jest tabela relacyjna o postaci

Kod

room_id (Twój crmid) | accessory_id (Twój relcrmid)
K_1 | Z_1
K_1 | Z_2
K_2 | Z_3

Co oznacza, że klient K_1 posiada 2 relacje (Z_1 i Z_2), a klient K_2, posiada tylko Z_3. Robię więc podzapytania, które zlicza mi liczbę poszczególnych relacji z tabeli trzymającej relacje, dla każdego wiersza Twojego klienta.

Spróbój tez zrobić aliasy, by podzapytanie nie korzystało z Twojego JOINa z linijki 7, do tego JOINa dodaj swoje powiązanie. Piszę z ręki, ale raczej powinno to wyglądać tak:

[SQL] pobierz, plaintext 
    SELECT 
    	vc.crmid AS id_klienta,
    	vc.relcrmid AS id_zdarzenia,
    	va.subject,
    	va.eventstatus 
    FROM vtiger_activity va
    JOIN vtiger_crmentityrel vc ON (vc.relcrmid = va.activityid)
    WHERE EXISTS (
    	SELECT vc2.relcrmid
    	FROM vtiger_crmentityrel vc2
    	WHERE vc2.relcrmid = va.activityid
    	GROUP BY vc2.relcrmid
    	HAVING COUNT(vc2.relcrmid) = 1
    )
[SQL] pobierz, plaintext 

Ten post edytował sabat24 30.10.2017, 23:28:22