SQLite where

Witam
Mam pewien problem z zapytaniem SQL:

 
SELECT first_name, last_name, count(scheduleevents.id)
FROM workers
LEFT JOIN scheduleevents
ON scheduleevents.worker_id = workers.id
WHERE is_start_time = 1 and is_active = 0 
and ( date < date() or ( date = date() AND time < time() )  ) 
GROUP BY workers.id 

Wynikiem tego zapytania są pracownicy z ilością 'scheduleevents' > 0 a powinni być pokazani wszyscy, nawet ci co mają 'scheduleevents' = 0.
Zauważyłem, że po skasowaniu całej klauzury WHERE zapytanie pokazuje tych z 0 jednak przez brak WHERE nie zlicza tak jak powinien.
Czy da się to jakoś przerobić by ten WHERE nie psuł zapytania?

Ło jeju... wiesz, że zapytania można formatować nie wstawiając spacji w losowe miejsca?

Twoje pytanie nie ma sensu także z innego powodu:

Wynikiem tego zapytania są pracownicy z ilością 'scheduleevents' > 0 (...)
Przecież schedulevents to tabela...

Zakładając jednak, że miałeś na myśli scheduleevents.id - wrzuć schemat bazy oraz jej dane. Najlepiej tu: http://sqlfiddle.com

strzelam, że któraś z tych kolumn należy do: scheduleevets:
is_start_time
is_active
date
time

Panczo napisał(a):

strzelam, że któraś z tych kolumn należy do: scheduleevets:
is_start_time
is_active
date
time

Wszystkie te kolumny należą do scheduleevents.

No to te kryteria nie powinny być w warunku where a warunku łączenia:

SELECT first_name, 
       last_name, 
       Count(scheduleevents.id) 
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.date < Date() 
                        OR ( scheduleevents.date = Date() 
                             AND scheduleevents.time < Time() ) ) 
GROUP  BY workers.id 

Wielkie dzięki, szczerze mówiąc nawet nie wiedziałem, że tak można.

Panczo napisał(a):

No to te kryteria nie powinny być w warunku where a warunku łączenia:

SELECT first_name, 
       last_name, 
       Count(scheduleevents.id) 
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.date < Date() 
                        OR ( scheduleevents.date = Date() 
                             AND scheduleevents.time < Time() ) ) 
GROUP  BY workers.id 

A gdybym chciał to rozbudować tak, żeby oprócz COUNT`a ogólnego pokazał mi np. COUNT roczny w sensie coś takiego:

SELECT first_name, 
       last_name, 
       Count(scheduleevents.id)
       (SELECT count(scheduleevents.id)
        FROM workers
               LEFT JOIN scheduleevents 
                      ON scheduleevents.worker_id = workers.id 
                         AND scheduleevents.is_start_time = 1 
                         AND scheduleevents.is_active = 0 
                         AND strftime('%Y', date) = strftime('%Y', date())
                         AND ( scheduleevents.date < Date() 
                                OR ( scheduleevents.date = Date() 
                                     AND scheduleevents.time < Time() ) ) 
        GROUP  BY workers.id)
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.date < Date() 
                        OR ( scheduleevents.date = Date() 
                             AND scheduleevents.time < Time() ) ) 
GROUP  BY workers.id 

Problem w tym, że po wykonaniu tego ten drugi select zwraca tylko jedną wartość taką samą dla wszystkich Pracowników. Jeśli powiedzmy 3 pracowników ma kolejno ogólnie eventów (10, 20, 30) to wartość roczna będzie taka jak pierwszego. Jeżeli ma 5 eventów w tym roku to każdy kolejny dostaje 5.

Problem w tym, że podzapytanie musi pytać o konkretne id z workers, czyli musisz dodać warunek:

SELECT first_name, 
       last_name, 
       COUNT(scheduleevents.id),
       (SELECT COUNT(scheduleevents.id)
        FROM workers as w
               LEFT JOIN scheduleevents 
                      ON scheduleevents.worker_id = w.id 
                         AND scheduleevents.is_start_time = 1 
                         AND scheduleevents.is_active = 0 
                         AND strftime('%Y', DATE) = strftime('%Y', DATE())
                         AND ( scheduleevents.DATE < DATE() 
                                OR ( scheduleevents.DATE = DATE() 
                                     AND scheduleevents.TIME < TIME() ) ) 
        WHERE 
                w.id = workers.id
        GROUP  BY workers.id)
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.DATE < DATE() 
                        OR ( scheduleevents.DATE = DATE() 
                             AND scheduleevents.TIME < TIME() ) ) 
GROUP  BY workers.id 

Zwróć uwagę n alias "w" dla tabeli workers w podzapytaniu, dzieki temu warunek WHERE w.id = workers.id, sprawia że podzapytanie odwołuje się do wiersza w którym jest wykonywane.

W tym przypadku podzapytanie nie wymaga w ogóle join-a:

SELECT first_name, 
       last_name, 
       COUNT(scheduleevents.id),
       (SELECT COUNT(scheduleevents.id)
        FROM scheduleevents 
        WHERE
                         AND scheduleevents.is_start_time = 1 
                         AND scheduleevents.is_active = 0 
                         AND strftime('%Y', DATE) = strftime('%Y', DATE())
                         AND ( scheduleevents.DATE < DATE() 
                                OR ( scheduleevents.DATE = DATE() 
                                     AND scheduleevents.TIME < TIME() ) ) 
                scheduleevents.worker_id = workers.id
       )
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.DATE < DATE() 
                        OR ( scheduleevents.DATE = DATE() 
                             AND scheduleevents.TIME < TIME() ) ) 
GROUP  BY workers.id 

Alternatywa to użycie wyrażenia case do obliczenia ilości w bieżącym roku:

SELECT first_name, 
       last_name, 
       COUNT(scheduleevents.id) ,
       SUM(case when strftime('%Y', DATE) = strftime('%Y', DATE()) then 1 else 0 end)
FROM   workers 
       LEFT JOIN scheduleevents 
              ON scheduleevents.worker_id = workers.id 
                 AND scheduleevents.is_start_time = 1 
                 AND scheduleevents.is_active = 0 
                 AND ( scheduleevents.DATE < DATE() 
                        OR ( scheduleevents.DATE = DATE() 
                             AND scheduleevents.TIME < TIME() ) ) 
GROUP  BY workers.id 

wyrażenie case when strftime('%Y', DATE) = strftime('%Y', DATE()) then 1 else 0 end dla bieżącego roku da 1 dla każdego innego 0, więc sumujemy ani liczymy.