SQLite can't find a way to avoid Cartesian Product

Question

I'm writing a query where I'm calculating sums of 2 different tables, which have a foreign key constraint (1:n).

So there's the table Kunde which is holding Customers. Every Customer is maintained by a Adm. Every Kunde has N different transactions (PbsRow), while every transaction contains N different products (WarengruppeVK). Every transaction has a Month and a Year (Monat and Jahr)

What I need is a result which contains the following information: 1) Name of an Adm, 2) Sum of all sollfracht and handling values which belong to one of the customers of this particular Adm in 2013, 3) Sum of all netto and db_basis values which belong to one of the PbsRows of that customer in 2013, 4 & 5) Same as 2) & 3) just in 2012

I have already tried different things, but I always end up having a Cartesian product with the sollfracht and handling values when I do the Join with the next Table.

Please have a look at my query:

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          SUM(vk.ZNETTO) AS summeVJ,
          SUM(vk.ZDB_BASIS) AS summeDBVJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(vk.ZNETTO) AS summeJ,
          SUM(vk.ZDB_BASIS) AS summeDBJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK

What can I do to avoid having this cartesian product? When I remove the WarengruppeVK-table from the result, the sollfracht and handling values are correct.

Thanks in advance.

EDIT: Here are some samples.

This is the result I'm getting from the query above:

And here's the result when I remove the very first join:

You'll notice that sollfrachtVJ and handlingVJ are different now. They are taken from PbsRow where the cartesian product happens. So these 2 values are actually the correct ones, but I also need the sums of the 2 values I have commented out.

And this here is the SQL statement after I have removed that one Join:

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          --          SUM(vk.ZNETTO) AS summeVJ,
--          SUM(vk.ZDB_BASIS) AS summeDBVJ,

          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM -- ZWARENGRUPPEVK vk
 ZPBSROW p -- LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW

   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(vk.ZNETTO) AS summeJ,
          SUM(vk.ZDB_BASIS) AS summeDBJ,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZWARENGRUPPEVK vk
   LEFT JOIN ZPBSROW p ON p.Z_PK=vk.ZPBSROW
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK

EDIT 2

Ok, here's the SQL statement which contains the correct result, but misses 4 columns.

SELECT vj.*,
       j.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT k.ZADMITARBEITER AS admidvj,
          SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
          SUM(p.ZHANDLING) AS handlingVJ
   FROM ZPBSROW p
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2012
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) vj,
  (SELECT k.ZADMITARBEITER AS admidj,
          SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
          SUM(p.ZHANDLING) AS handlingJ
   FROM ZPBSROW p
   LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
   WHERE ZJAHR=2013
     AND ZMONAT>=1
     AND ZMONAT<=6
   GROUP BY k.ZADMITARBEITER) j
WHERE vj.admidvj=j.admidj
  AND vj.admidvj=adm.Z_PK

As you can see, summeJ, summeVJ, summeDBJ and summeDBVJ are not included, that's where the problem is. All values in this result are correct, but I also need to have these 4 values in my result. The first screenshot of my results above contain correct summeJ, summeVJ, summeDBJ and summeDBVJ values, but incorrect handlingJ, handlingVJ, sollfrachtJ and sollfrachtVJ values.

EDIT 3:

I finally found a way to do this. Here's the query that works. It's just a couple of subqueries:

    SELECT ((summeJ-summeVJ)/summeVJ*100) AS abwNetto,
       (summeJ-summeVJ) AS abwNettoAbs,
       ((summeDBJ-summeDBVJ)/summeDBVJ*100) AS abwDB,
       (summeDBJ-summeDBVJ) AS abwDBAbs,
       t0.*,
       t1.*,
       adm.ZNAME
FROM ZADM adm,
  (SELECT vj.*,
          j.*
   FROM
     (SELECT k.ZADMITARBEITER AS admidvj,
             SUM(p.ZSOLLFRACHT) AS sollfrachtVJ,
             SUM(p.ZHANDLING) AS handlingVJ
      FROM ZPBSROW p
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2012
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) vj
   LEFT JOIN
     (SELECT k.ZADMITARBEITER AS admidj,
             SUM(p.ZSOLLFRACHT) AS sollfrachtJ,
             SUM(p.ZHANDLING) AS handlingJ
      FROM ZPBSROW p
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2013
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) j ON vj.admidvj = j.admidj) t0,
  (SELECT vj.*,
          j.*
   FROM
     (SELECT k.ZADMITARBEITER AS admidvj,
             SUM(vk.ZNETTO) AS summeVJ,
             SUM(vk.ZDB_BASIS) AS summeDBVJ
      FROM ZPBSROW p
      LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=p.Z_PK
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2012
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) vj
   LEFT JOIN
     (SELECT k.ZADMITARBEITER AS admidj,
             SUM(vk.ZNETTO) AS summeJ,
             SUM(vk.ZDB_BASIS) AS summeDBJ
      FROM ZPBSROW p
      LEFT JOIN ZWARENGRUPPEVK vk ON vk.ZPBSROW=p.Z_PK
      LEFT JOIN ZKUNDE k ON k.Z_PK=p.ZKUNDE
      WHERE ZJAHR=2013
        AND ZMONAT>=1
        AND ZMONAT<=6
      GROUP BY k.ZADMITARBEITER) j ON vj.admidvj = j.admidj) t1
WHERE t0.admidvj=t1.admidvj
  AND t0.admidvj=adm.Z_PK

Answer 1

The problem with your joins is that everything is joined together. You should use independent scalar subqueries instead:

SELECT name,
       (SELECT SUM(WarengruppeVK.netto)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        JOIN WarengruppeVK ON PbsRow.PK = WarengruppeVK.pbsrow
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2012
       ) AS vj_netto,
       (SELECT SUM(PbsRow.sollfracht)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2012
       ) AS vj_sollfracht
       (SELECT SUM(WarengruppeVK.netto)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        JOIN WarengruppeVK ON PbsRow.PK = WarengruppeVK.pbsrow
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2013
       ) AS j_netto,
       (SELECT SUM(PbsRow.sollfracht)
        FROM Kunde
        JOIN PbsRow ON Kunde.PK = PbsRow.kunde
        WHERE Kunde.admitarbeiter = Adm.PK
          AND PbsRow.jahr = 2013
       ) AS j_sollfracht
FROM Adm