mysql 4.1.0 nested query probleem

[Haranaka]

Ik heb een database met daarin de volgende tables/gegevens:

De table Users bevat een NickName en een UserID. De table Hosts bevat een HostID, een HostName en een owner (is zelfde als Users.UserID). En dat een table Flushes, die bevat Size, Flushdate en Host (is gelijk aan Hosts.HostID).

Deze tables kunnen heel mooi gejoined worden zodat er een overzicht komt van NickName en de Size.

Deze querie gebruik ik om het totaal van iedere NickName te berekenen:

code:

SELECT
                    t1.NickName,
                    sum(t3.Size) as Total
                FROM
                    Users t1 left join Hosts t2 on t1.UserID=t2.Owner
                    left join Flushes t3 on t2.HostID=t3.Host
                WHERE
                    t3.Size>0
                  AND
                    t3.Flushdate<current_date
                GROUP by t1.UserID
                ORDER by Total DESC

En deze gebruik ik om voor iedrre Nickname de Size uit te rekeken van gister:

code:

SELECT
                    t1.NickName,
                    sum(t3.Size) as Total
                FROM
                    Users t1 left join Hosts t2 on t1.UserID=t2.Owner
                    left join Flushes t3 on t2.HostID=t3.Host
                WHERE
                    t3.Flushdate=subdate(current_date, intereval 1 day)
                GROUP by t1.UserID
                ORDER by Total DESC

Dat werkt allemaal wel, maar ik zou deze gegens graag in 1 querie aan elkaar plakken zodat ik gelijk kan zien dat bv Pietje een total heeft van 3423 en gister 46 heeft gedaan.

Ik dacht dat dus te doen met een nested query. Maar veel succes leverde dat niet op. Na een halve dag ploeteren kom ik niet verder dan dit:

code:

SELECT 
                    t1.NickName, 
                    sum(t3.Size) as Total, 
                       (select sum(t4.Size) FROM Flushes t4 
                      WHERE
                         Flushdate="2003-03-22" AND t4.Host=t3.Host) as output 
              FROM 
                    Users t1 left join Hosts t2 on t1.UserID=t2.Owner 
                    left join Flushes t3 on t2.HostID=t3.Host 
              WHERE 
                    t3.Flushdate<current_date 
              GROUP BY
                     t1.UserID ORDER by Total DESC;

Het probleem hierbij is dat de geneste querie niet goed werkt. Die sum(t4.Size) die daar staat pakt alleen van 1 Host de sum, en die niet voor alle Hosts die bij een NickName horen. Eigenlijk zou die t4 gejoined moeten worden, maar dat lukt me niet.
Als ik " AND t4.Host=t3.Host" weghaal dan pakt hij de sum van alle Sizes van die datum en niet per NickName.

Met andere woorden HELP ik kom er niet uit... Mijn hoop is gevestigd op een geniale geest die hier rond wandeld.

[jochemd]

code:

SELECT
   t1.NickName,
   SUM(t3.Size) as Total,
   SUM(
      CASE
         WHEN t3.Flushdate=subdate(current_date, intereval 1 day) THEN t3.Size
         ELSE 0
      END
   ) AS yesterday
FROM
   Users t1 LEFT JOIN Hosts t2 ON t1.UserID=t2.Owner
   LEFT JOIN Flushes t3 ON t2.HostID=t3.Host
WHERE
   t3.Flushdate=subdate(current_date, intereval 1 day)
GROUP BY t1.UserID
ORDER BY Total DESC

Alleen nog even kijken hoe die zich gedraagd met NULLs.

[paulh]

Ik zal er nog maar niet al te hard op rekenen dat de queries die schrijft goed worden uitgevoerd door mysql 4.1 .... het is nog heel erg alpha.

[Night-Reveller]

Ik heb het ff nagemaakt in MSSQL en daaruit kreeg ik helvolgende werkend. Hierbij heb ik wel een Primairy Key aangemaakt in de tabel Flushes, nl. FlushID, maar dat doet er verder niet toe.

Jouw gewenste resultaat heb ik gemaakt door 2 aparte subqueries te maken, die de tabellen [HostID, 'Total'] respectievelijk [HostID, 'Gisteren'] opleveren. Deze twee tabellen gecombineerd met de tabel Users...et volia!

SQL:

code:

SELECT
    U.NickName,
    Overall.Total as 'Overall',
    Yesterday.Total as 'Yesterday'
FROM
    Users U 
    left Outer join 

    -- Tabel met totalen van flushes
    (Select 
        H.HostID, 
        SUM(TF.Size) as Total
    From 
        Hosts H 
        Left Outer Join Flushes TF on H.HostID = TF.Host
    Group By 
        H.HostID
    ) as Overall on U.UserID = Overall.HostID
    
    -- Tabel met daily flushes
    left Outer Join
    (Select  
        H.HostID,
        SUM(DF.Size) as Total
    From
        Hosts H
        Left Outer Join Flushes DF on H.HostID = DF.Host
    Where 
        DF.Flushdate = '01-02-01'
    Group By 
        H.HostID
    ) as Yesterday on U.UserID = Yesterday.HostID 
Order By 
    'Overall' DESC

Opm(1): Vervang '01-02-01' in MySQL door de code die de datum van gisteren oplevert en het zou moeten werken. Waarschijnlijk ook wel in de (alpha-like) MySQL 4.1.
Opm(2): Daarnaast kreeg ik het niet voor elkaar in MSSQL om de subqueries een alias te geven, waardoor ik genoodzaakt was om de complete namen te gebruiken (Overall en Yesterday). Wellicht kan dit wel in MySQL.

Als resultaat krijg je dan bijvoorbeeld:

Nickname	Overall	Yesterday
Diederik	250	150
Bastiaan	35	20
Koen	2	NULL

Mocht je de NULL niet mooi vinden, dan kan je proberen of MySQL een CASE-statement o.i.d. kent. Hiermee kan je, zoals jochemd laat zien, een soort van if-statement toevoegen:

code:

SUM (Case When Yesterday.Total is null then 0 end) as 'Yesterday'

asjeblieft

[Annie]

Night-Reveller schreef op 18 April 2003 @ 21:50:

code:

SUM (Case When Yesterday.Total is null then 0 end) as 'Yesterday'

asjeblieft

Waarom niet

SQL:

SUM (ISNULL (Yesterday.Total, 0) ) as 'Yesterday'

Of

SQL:

SUM (COALESCE (Yesterday.Total, 0) ) as 'Yesterday'

[Goodielover]

Waarom gebruik je een left join. terwijl je alleen records selecteert waarnij je een normale join kan gebruiken.

[Night-Reveller]

Hmmm. Ik heb er nog even over na zitten te denken, maar ik heb een verkeerde aanname gedaan (doh! Aannames zijn altijd gevaarlijk ). Ik ging er van uit dat een User één host heeft, maar het kunnen er natuurlijk ook meerdere zijn...*zucht*

Dus i.pv. groeperen op H.HostID in de twee subqueries, moet er nu gegroepeerd worden op H.Owner, welke dezelfde was als Users.UserID (zie openingspost). Dan wordt de query:

code:

SELECT
    U.NickName,
    Overall.Total as 'Overall',
    Yesterday.Total as 'Yesterday'
FROM
    Users U 
    left Outer join 

    -- Tabel met totalen van flushes
    (Select 
        H.Owner, 
        SUM(TF.Size) as Total
    From 
        Hosts H 
        Left Outer Join Flushes TF on H.HostID = TF.Host
    Group By 
        H.Owner
    ) as Overall on U.UserID = Overall.Owner
    
    -- Tabel met daily flushes
    left Outer Join
    (Select  
        H.Owner,
        SUM(DF.Size) as Total
    From
        Hosts H
        Left Outer Join Flushes DF on H.HostID = DF.Host
    Where 
        DF.Flushdate = '01-02-01'
    Group By 
        H.Owner
    ) as Yesterday on U.UserID = Yesterday.Owner 
Order By 
    'Overall' DESC

Oh en Annie, je hebt gelijk, dat maakt het idd makkelijker.