Wyświetlanie użytkowników z zamkniętymi pożyczkami Opcje

[sebekzosw]

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(15) COLLATE utf8_polish_ci DEFAULT NULL COMMENT 'numer umowy',
  `status` enum('1','2','3') COLLATE utf8_polish_ci NOT NULL DEFAULT '1' COMMENT 'status umowy',
  `cid` int(10) UNSIGNED DEFAULT '0' COMMENT 'id klienta',
  `startLoan` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data rozpoczęcia umowy',
  `endLoan` date DEFAULT NULL COMMENT 'data zakończenia umowy',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=42 ;
[SQL] pobierz, plaintext 

mam taką tabelę z umowami klientów. Jeden użytkownik (cid) może mieć wiele umów. Chciałbym wyświetlić użytkowników, którzy mają zamknięte wszystkie umowy (status = 3)

najlepiej jednym zapytaniem. Nie mam pomysłu na rozwiązanie tego. Ilość wszystkich umów musi być równa sumie umów z statusem 2 i 3

Ten post edytował sebekzosw 12.07.2013, 11:24:52

[nospor]

group by + having

[sebekzosw]

Próbowałem z czymś takim, ale nie działa coś :/

[SQL] pobierz, plaintext 
SELECT * FROM `clients` AS `c`
LEFT JOIN `loans` AS `l` ON `l`.`cid` = `c`.`id`
LEFT JOIN `loans` AS `l2` ON `l2`.`cid` = `c`.`id` AND `l2`.`status` IN ('2','3')
GROUP BY `c`.`id`
HAVING count( `l`.`id` ) = count( `l2`.`id` )
[SQL] pobierz, plaintext 

[mar1aczi]

Potrzebujesz identyfikator tych klientów

[SQL] pobierz, plaintext 
SELECT DISTINCT cid FROM loans WHERE STATUS IN (2,3);
[SQL] pobierz, plaintext 

czy może liczby - ile jest z "2" a ile z "3"?

[sebekzosw]

Potrzebuje wszystkie dane z tabeli `clients` użytkowników, którzy mają ilość wszystkich umów równą ilości umów umów ze statusem 2 i 3 => to mi da listę użytkowników bez otwartych pożyczek.

[mar1aczi]

[SQL] pobierz, plaintext 
SELECT * FROM 
(SELECT l.cid, l.STATUS, count(*) s2
FROM loans l
WHERE l.STATUS IN ('2')
GROUP BY l.cid, l.STATUS) l2,
(SELECT l.cid, l.STATUS, count(*) s3
FROM loans l
WHERE l.STATUS IN ('3')
GROUP BY l.cid, l.STATUS) l3
WHERE l2.cid = l3.cid AND l2.s2 = l3.s3;
[SQL] pobierz, plaintext 

pokaże użytkowników, których liczba umów w statusie 2 jest równa liczbie umów w statusie 3.
O to Ci chodziło?

Ten post edytował mar1aczi 12.07.2013, 14:08:49

[sebekzosw]

Liczba umów ze statusem 2 i 3 jest równa ilości umów że statusem 1, 2 i 3

[mar1aczi]

To podałem Ci punkt zaczepienia. Zmodyfikuj pod siebie zapytanie podane w moim poprzednim poście.

Edycja:
Nie wygodniej sprawdzić, czy po prostu użytkownik nie ma umów w statusie 1?

Ten post edytował mar1aczi 12.07.2013, 14:53:03

[mmmmmmm]

[SQL] pobierz, plaintext 
SELECT * FROM loans WHERE cid IN (SELECT cid FROM loans GROUP BY cid HAVING count(id)=count(case when  STATUS IN (2,3) then id end) )
[SQL] pobierz, plaintext 

[sebekzosw]

ja wymyśliłem coś takiego:

[SQL] pobierz, plaintext 
SELECT  `c` . * 
FROM  `clients` AS  `c` 
LEFT JOIN  `loans` AS  `l` ON  `l`.`cid` =  `c`.`id` AND  `l`.`status` =  '1'
WHERE `l`.`id` IS NULL 
[SQL] pobierz, plaintext 

zgodnie ze sugestią mar1aczi.

z tabeli clients biorę odpowiednie dane o kliencie, a z tabeli loans potrzebuje jeszcze kolumnę closeLoan. Jakieś sugestie? ;-)

mam!:

[SQL] pobierz, plaintext 
SELECT  `c` . * , `l`.`closeLoan`, `l`.`status`
FROM  `clients` AS  `c` 
LEFT JOIN (
    SELECT * FROM `loans` ORDER BY `id` DESC
) `l` 
ON `l`.`cid` = `c`.`id`
GROUP BY `c`.`id`
HAVING `l`.`status` = '3'
[SQL] pobierz, plaintext 

Ten post edytował sebekzosw 14.07.2013, 17:43:21