SQL "umbauen"

[Salomon]

Hallo,
mit dem unten aufgeführten SQL bekomme ich die Anzahl der Issues die ein Team an dem Tag X eröffnet hat. Das funktioniert soweit super. Allerdings müsste ich das SQL so haben, das auch die Teams in der Ergebnismenge sind, die keine Issues eröffnet haben. Diese sollen halt mit 0 dastehen. Wie bekomme ich das hin ??

Das ganze brauche ich, da ich das Ergebnis in einem Balkendiagram in FastReport anzeigen möchte, und dort alle Teams sichtbar sein sollen.

SQL-Code:

select t.teamID, count(i.issueID) as TotalIssues
from tb_teams t left join tb_users u on u.fk_teamid = t.teamid
left join tb_issues i on i.fk_userid = u.userid

where 
i.fk_projectid = 1 
and i.isdeleted = 0
and CONVERT(varchar(8), '26.02.2008 00:00:00', 104) = CONVERT(varchar(8), CreationDate, 104) 

group by t.teamid
order by t.teamid

Thanx
Marcus

[NormanNG]

Hi,

also zuerst fällt mir das Konstrukt

SQL-Code:

and CONVERT(varchar(8), '26.02.2008 00:00:00', 104) = CONVERT(varchar(8), CreationDate, 104)

auf. Warum vergleichst du nicht direkt die Datum-Spalten bzw. verwendest DateDiff?

Zur Frage selbst:
Wenn du mit left joins die Issues dazuholst, dann sind für alle Team, die keine Issues haben, die entsprechenden Issue-Spalte gleich NULL. Dann sind aber alle WHERE-Bedingungen, die auf Issue-Spaltenwerte prüfen falsch...

Also muss du die Spaltenwerte auch auf Null prüfen, etwa so:

SQL-Code:

where
(i.fk_projectid = 1 and i.isdeleted = 0)
or
(i.fk_projektid is null)

[mkinzler]

Versuchs mal mit einem right join

[Salomon]

Der right join führt zu dem selben Ergebnis.

@NormanNG: Mit dem wilden Konstrukt habe ich testweise alle Issues eines Tages gefiltert.

Das Prüfen auf NUll bekomme ich leider auch nicht hin. Selbst wenn ich alle Where Bedingungen bis auf die Datumseinschränkung auskommentiere bekomme ich ja nur den IssueCount für Teams die mindestens eine Issue erstellt haben.

[NormanNG]

Hi,

also mit der WHERE-Clause

SQL-Code:

where
( (i.fk_projectid = 1 and i.isdeleted = 0)
  or (i.fk_projektid is null) )
and 
( (CONVERT(varchar(8), '26.02.2008 00:00:00', 104) = CONVERT(varchar(8), CreationDate, 104))
  or (CreationDate is null)
)

funktioniert das bei mir (mit ein paar Testdaten).

Wie sieht denn dein SQL jetzt aus?

[edit]P.S.: Aus welcher Tabelle kommt denn CreationDate? Wenn das aus tb_issues kommt gilt das Gleiche wie oben!

[Salomon]

Hey,
mmh, so gehts bei mir leider auch nicht. Die übrigen Where Bedingungen können wir eigentlich weglassen. Allerdings schränkt das Datum die Ergebnismenge so ein, das die anderen Teams fehlen. Mein SQL sieht momentan so aus:

SQL-Code:

select t.name, count(i.issueID) as TotalIssue
from tb_teams t full join tb_users u on u.fk_teamid = t.teamid
full join tb_issues i on i.fk_userid = u.userid
where    
 CONVERT(varchar(8), '26.02.2008 00:00:00', 104) = CONVERT(varchar(8), CreationDate, 104)
group by t.name
order by t.name

Ich habe mal die Tabellen als Diagramm angehängt. Dann seht ihr genau wie sie aussehen. An das Team einer Issue kommt man nur über den User der in FK_UserID steht.

[NormanNG]

Hi,

Sorry, mein Edit oben kam zu spät.

Das CreationDate kommt ja auch aus tb_issues. Dann muss das auch auf Null geprüft oder anders geklammert werden...
siehe oben.

[NormanNG]

Hi,

hier mal eine Abfrage, die laufen sollte:

SQL-Code:

select t.name, count(i.issueID) as TotalIssue
from tb_teams t 
left join tb_users u on u.fk_teamid = t.teamid
left join tb_issues i on i.fk_userid = u.userid
where   
( CONVERT(varchar(8), '26.02.2008 00:00:00', 104) = CONVERT(varchar(8), CreationDate, 104) )
or
( CreationDate is null )
group by t.name
order by t.name

... left join!

[Salomon]

Hey super, danke für deine Hilfe :)

Das funktioniert! Jetzt muss ich das nur noch richtig verstehen...

Ich muss also die Werte die aus der TB_Issues kommen auch noch auf NULL prüfen?

Mit left join gehts scheinbar auch.

[NormanNG]

Hi,

Zitat:

Jetzt muss ich das nur noch richtig verstehen...

Ist eigendlich ganz einfach:

Du holst dir Daten aus einer Tabelle (from ..., hier tb_team).
Dann legst du immer dann Daten aus anderen Tabellen dazu, wenn sie "passen" (...join...on...). Wenn Sie nicht passen, dann sind alle Spalten dieser Tabelle NULL (left join).
In der Where-Bedingung kann man nun Spalten dieser gejointen-Tabellen prüfen. Wenn aber auf einen exakten Wert geprüft wird (i.fk_projectid = 1), dann sind damit alle Zeilen der Ergebnismenge ausgeschlossen, die hier NULL haben (NULL<>1).

Du wolltest aber auch genau diese Zeilen zeigen/zählen, bei denen keine Daten aus den gejointen Tabellen vorhanden sind (i.e. ein Team ohne Issues). Also muss du hier immer den Fall NULL berücksichtigen.

[Salomon]

Hey,
das ich über die Joins eine große Datenmenge "baue" war mir klar. Das die Spalten auf NULL stehen wenn sie nicht passen war mir nicht so bewust. Ohne die NULL Prüfung konnte ich dann gar nicht an die Daten kommen.

[Salomon]

Hi all,
ich muss den Thread noch einmal reaktivieren. Mir ist aufgefallen, das in der Ergebnismenge nicht alle Teams aus der TeamTabelle auftauchen. Drei fehlen. Allerdings sehe ich nicht woran das liegen kann :(

Nehme ich die zeitliche Einschränkung raus, sind alle Teams dabei. Etwas weiter oben habe ich den DB Aufbau angehängt.

SQL-Code:

select t.name, count(i.issueID) as TotalIssue 
from tb_teams t 
full join tb_users u on u.fk_teamid = t.teamid 
full join tb_issues i on i.fk_userid = u.userid 
where
((i.CreationDate >= '03.03.2008 00:00:00')
 and  (i.CreationDate < '09.03.2008 00:00:00')) or (i.CreationDate is null)  

group by t.name 
order by t.name

Thanx Marcus

[Olli73]

Hallo,

ich nehme mal an, dass die Teams, die gar keine Issues und die Teams, die Issues im genannten Zeitraum haben auftauchen, aber nicht die, die Issues in einem anderen Zeitraum haben. Wäre zumindest logisch, wenn man sich vorstellt, dass zuerst der JOIN ausgeführt wird und anschließend deine Bedingungen darüber laufen

Versuch doch mal die Bedingung mit in den Join reinzunehmen:

SQL-Code:

select t.name, count(i.issueID) as TotalIssue 
from tb_teams t 
full join tb_users u on u.fk_teamid = t.teamid 
full join tb_issues i on i.fk_userid = u.userid and 
  (i.CreationDate >= '03.03.2008 00:00:00') and  (i.CreationDate < '09.03.2008 00:00:00')
group by t.name 
order by t.name

[NormanNG]

Hi,

es macht keinen Unterschied, ob die Bedingungen direkt im Join oder im Where gesetzt sind.
Welche Daten fehlen denn?

[edit]
ich seh´gerade, du machst full joins und prüft dann auf [(i.CreationDate is null)].
Kann es sein, dass die Teams fehlen, die garkeine issues haben?
Versuchs doch mal mit LEFT JOIN...

[Olli73]

Zitat:

es macht keinen Unterschied, ob die Bedingungen direkt im Join oder im Where gesetzt sind.

Bist du sicher bzw. hast du es getestet. Bei mir macht es nämlich (mit anderen Daten) einen Unterschied.

[alzaimar]

Du willst also (1) Teams auflisten, und wenn die 'issues' haben, dann diese Zählen. Oder willst Du (2) nur die Teams aufzählen, die innerhalb des Zeitraumes 'issues' hatten und dann diese Teams und deren Anzahl auflisten?

Zu 1. fällt mir spontan sowas ein:

SQL-Code:

select t.name,
       coalesce (
         (select count (distinct i.issueID) 
            from tb_issues i 
                 join tb_users u on i.fk_userID = u.userID 
           where u.fk_teamID = t.teamID 
             and i.CreationDate Between '20080303' and '20080309 23:59:59')
         ,0) as Anzahl
from tb_teams t
order by t.name

Hier werden garantiert alle Teams aufgelistet sowie deren Issueanzahl im gewünschten Zeitraum.
Und zu 2.

SQL-Code:

select t.name, 
       count(i.issueID) as TotalIssue
from tb_teams t
     join tb_users u on u.fk_teamid = t.teamid
     join tb_issues i on i.fk_userid = u.userid
where i.CreationDate Between '20080303' and '20080309 23:59:59'
group by t.name
order by t.name

[NormanNG]

Hi,

Zitat:

Zitat von Olli73

Zitat:

es macht keinen Unterschied, ob die Bedingungen direkt im Join oder im Where gesetzt sind.

Bist du sicher bzw. hast du es getestet. Bei mir macht es nämlich (mit anderen Daten) einen Unterschied.

@Olli73
Das dachte ich bisher immer :gruebel:
Hast du mal ein Beispeil für mich?

[Olli73]

Zitat:

Hast du mal ein Beispeil für mich?

@NormanNG:

Ich veruche mal ein einfaches Beispiel mit 2 Tabellen und wenigen Daten zu konstruieren:

SQL-Code:

Tabelle1 mit TeamA, TeamB, TeamC und Tabelle 2 mit Issues, wobei TeamA und TeamB jeweils 1 und TeamC keinen Issue hat:

select a.*, b.* from tabelle1 a left join tabelle2 b on b.team = a.team
ergibt dann:

TeamA 28.04.2008 ...
TeamB 30.04.2008 ...
TeamC <NULL>     ...

wenn ich dann anfüge "where b.datum = '28.04.2008' or b.datum is null" wird obiges Ergebnis(!) eingeschränkt und TeamB fällt dadurch weg, weil es die Bedingung nicht erfüllt:

TeamA 28.04.2008 ...
TeamC <NULL>     ...

schreibe ich aber anstatt(!) "where ..." dann "AND b.datum = '28.04.2008' wird die Bedingung bereits beim Aufbau des Joins(!) ausgewertet und man erhält:

TeamA <NULL>     ...
TeamB 30.04.2008 ...
TeamC <NULL>    ...

Ich hoffe mal, dass ich mich verständlich ausgedrückt habe.

[NormanNG]

Hi,

du schummelst :wink:


im Original stand ein full-join, und darauf bezog sich meine Anmerkung.

SQL-Code:

select t.name, count(i.issueID) as TotalIssue
from tb_teams t
full join tb_users u on u.fk_teamid = t.teamid
full join tb_issues i on i.fk_userid = u.userid and
  (i.CreationDate >= '03.03.2008 00:00:00') and  (i.CreationDate < '09.03.2008 00:00:00')
group by t.name
order by t.name

Bei einem left-join hat du natürlich Recht.

[Salomon]

@alzaimar: Dein Fall 1 ist genau das was ich suche :) Damit mir sowas auch mal spontan einfällt habe ich mir erstmal ein SQL Buch bestellt ;) Auf die Abfrage wäre ich wohl so nie gekommen.
Ich brauche immer alle Teams, da ich in einem Report alle geschlossen den geöffneten Issues pro Team gegenüberstellen will.


Wenn ich eine weitere Tabelle joine, muss ich dann Felder darin dann auch auf NULL abfragen? Ich glaube nicht, oder? Sorry, wenn das eine "komische" Frage ist. Für mich ist das aber schon ein recht komplexes SQL Querry.

SQL-Code:

set transaction isolation level read uncommitted;     
select t.name as TeamName, 
       coalesce ( 
         (select count (distinct i.issueID) 
            from tb_issues i 
                 join tb_users u on i.fk_userID = u.userID
                 join tb_history h on h.fk_issueid = i.issueid 
           where u.fk_teamID = t.teamID 
                and ((i.visibility = 1) or i.visibility is null)  <== Auf NULL prüfen?                                    
                and ((h.action = 1) or h.action is null)
                and ((i.FK_IssueStatusID = 5) or i.FK_IssueStatusID is null)                                  
  
                and ((h.ChangeDate  >= '03.03.2008 00:00:00' /* -1 */ and h.ChangeDate  < '09.03.2008 00:00:00')    
                 or (h.ChangeDate  is null))                   
             ),0) as Anzahl 
from tb_teams t 
order by t.name

Das ich während des Joins schon die Datenmenge einschränken kann wusste ich auch noch nicht, hier lernt man doch immer viel dazu :) Die verschiedenen Join Varianten hatte ich in meinem ursprünlichen Query fast alle durchprobiert. Der Full oder left join brachten da das selbe Ergebnis.

[alzaimar]

Erstmal ist es eine gute Idee, sich ein Buch zu besorgen. Ich habe 'Inside SQL xxxxx' (xxxxx = Versionsnummer vom MSSQL) von Kalen Delaney (MS Press). Das arbeitest Du durch und dann stellst Du keine Fragen mehr ;-)

Nimm Dir einfach die innere Query (die, die zählt) und ersetze das 'Count(*)' durch ein '*'. Dann siehst du, was alles gezählt werden WÜRDE. Ich mache es so:
1. Nur die Einschränkung auf ein Team
2. Dann zusätzlich die Einschränkung auf den Zeitraum (oder beides gleich)
3. Gibt es Einträge mit 'visibility = NULL'? Willst Du die mitzählen?
usw.

Ich verfeinere also die Abfrage, bis sie passt und versuche dabei, Sonderfälle gleich mit einzubauen. Das geht am Besten, in dem ich einen großen Zeitraum wähle.

Die Geschichte mit dem LEFT, RIGHT und FULL JOIN (oder ganz ohne) lässt sich einfach so erklären:

Zwei Tabellen: Paare und Kinder. Es gibt kinderlose Paare, Paare mit Kindern und Waisenkinder.

1. Alle Paare und, falls sie Kinder haben, auch Diese: Paare LEFT JOIN Kinder
2. Nur Paare mit Kindern: Paare JOIN Kinder
3. Alle Kinder, und falls sie Eltern haben, auch Diese: Paare RIGHT JOIN Kinder
4. Nur Paare ohne Kinder: Paare LEFT JOIN Kinder WHERE Kinder.Kind IS NULL
5. Nur Waisenkinder: Paare RIGHT JOIN Kinder WHERE Paare.Paar IS NULL

(Die ON - Klausel (Paare JOIN Kinder ON Paar.paarID = Kinder.paarID) habe ich weggelassen.
Anmerkungen:
A LEFT JOIN B ist das Gleiche wie B RIGHT JOIN A
(2) kann man auch so ausdrücken: Paare LEFT JOIN Kinder WHERE Kinder.Kind is not null. Auf Deutsch bedeutet das:
"Alle Paare, und falls sie Kinder haben, auch Diese WOBEI es ein Kind geben muss"


Ach, nochwas:

Die ON-Klausel dient zur logischen Verknüpfung der Tabellen und NICHT zum Filtern, das macht man in der WHERE-Klausel.
Es gibt aber auch Filter, die auch eine logische Verknüpfung sind, also ist da die Trennung nicht so klar.


Und nochwas:
JOINs über zwei Tabellen sind einfach, ab drei wirds dann haarig, daher ist es wichtig, möglichst nur in der WHERE-Klausel zu filtern.

[Salomon]

Ich habe mir das Buch "Einstieg in SQL" bestellt. Ich denke das bietet einen guten Einstieg.

Vielleicht solltest du auch ein Buch schreiben, deine Erklärungen kann man auf jedenfall gut nachvollziehen :)

Das langsame herrantasten an den kompletten SQL String funktioniert ganz gut. Da merkt man schnell das ich viele der NULL-Abfragen nicht brauche.

[Olli73]

Zitat:

du schummelst
im Original stand ein full-join, und darauf bezog sich meine Anmerkung.

@NormanNG:

Im Beispiel habe ich zwar left join verwendet, weil full join m.E. u.U. auch unerwünschte Daten bringt, aber es ging ja darum, dass nicht alle Teams aufgelistet wurden; bei meiner Abfrage aber kommen alle Teams vor:

SQL-Code:

create table TEST1 (TEAM varchar(10));
create table TEST2 (TEAM varchar(10), DATUM datetime);

insert into TEST1 values ('TeamA');
insert into TEST1 values ('TeamB');
insert into TEST1 values ('TeamC');

insert into TEST2 values ('TeamA', '29.04.2008');
insert into TEST2 values ('TeamB', '30.04.2008');
insert into TEST2 values (null,    '29.04.2008');
insert into TEST2 values (null,    '30.04.2008');


mit "where" fehlt TeamA komplett:

select a.TEAM, b.DATUM from TEST1 a full join TEST2 b on b.team = a.team
where (b.DATUM = '30.04.2008' or b.DATUM is null) order by 1, 2

<NULL>  30.04.2008
TeamB   30.04.2008
TeamC   <NULL>

bei der Bedingung im Join erscheinen alle Teams und ggf. noch ein paar
unnötige Datumswerte mit Team "NULL" (welche bei left join fehlen würden):

select a.TEAM, b.DATUM from TEST1 a full join TEST2 b on b.team = a.team
and (b.DATUM = '30.04.2008') order by 1, 2

<NULL>  29.04.2008
<NULL>  29.04.2008
<NULL>  30.04.2008
TeamA   <NULL>
TeamB   30.04.2008
TeamC   <NULL>

Aber auf jeden Fall fehlt kein Team - im Gegensatz zu "where ..."!

Daher ist zumindest die Aussage

Zitat:

es macht keinen Unterschied, ob die Bedingungen direkt im Join oder im Where gesetzt sind

widerlegt.

Gruß
Olli