How to turn WHERE into JOIN?

Question

SQL

There is one that doesn't work due to nested SELECT:

SELECT
    sum(Acheck."TOTAL") AS SUMM,
    strftime('%Y - %m', LOGICDATE) AS YearMonth
FROM 
    Acheck
WHERE Acheck.SYS_NUM IN (
    SELECT 
        ARcheck.SYS_NUM
    FROM
        ARcheck
    WHERE
        ARcheck.SIFR IN (
            SELECT 
                Menu.SIRF AS MenuSIRF, 
                Menu.Name AS MenuName,
                Categ.SIRF
            FROM
                Menu Join Categ on Menu.CATEG = Categ.SIRF
            WHERE (Categ.SIRF = 2)
    )
)
GROUP BY strftime('%Y %m', LOGICDATE);

I'm trying to kill it on JOIN:

SELECT 
   sum(AC."TOTAL") AS SUME,
   strftime('%Y - %m', AC.LOGICDATE) AS LOGICDATE
FROM
    Menu 
    JOIN Categ
        ON Menu.CATEG = Categ.SIRF
        AND Categ.SIRF = 2
    JOIN ARcheck AR
        ON AR.SIFR = Menu.SIRF
    JOIN Acheck AC
        ON AC.SYS_NUM = AR.SYS_NUM

GROUP BY strftime('%Y %m', AC.LOGICDATE);

Nothing comes out. Where am I wrong?
sqlfiddle.com/#!5/50a93/2

Answer 1

The first option does not work for you, not because of nesting, but because you select three columns in SELECTE, and then you try to use this SELECT in IN, where only one column is allowed.
The JOIN option can be done in different ways, for example like this:

SELECT sum(`Acheck`.`TOTAL`) AS `SUME`,
       strftime('%Y - %m', `Acheck`.`LOGICDATE`) AS `LOGICDATE`
  FROM `Categ`
    JOIN `Menu` ON `Categ`.`SIRF` = `Menu`.`Categ`
    JOIN `ARcheck` ON `Menu`.`SIRF` = `ARcheck`.`SIFR`
    JOIN `Acheck` ON `ARcheck`.`SYS_NUM` = `Acheck`.`SYS_NUM`
  WHERE `Categ`.`SIRF` = 2
  GROUP BY `LOGICDATE`

Or like this:

SELECT sum(`Acheck`.`TOTAL`) AS `SUME`,
       strftime('%Y - %m', `Acheck`.`LOGICDATE`) AS `LOGICDATE`
  FROM `Acheck`
    JOIN `ARcheck` ON `ARcheck`.`SYS_NUM` = `Acheck`.`SYS_NUM`
    JOIN `Menu` ON `Menu`.`SIRF` = `ARcheck`.`SIFR`
    JOIN `Categ` ON `Categ`.`SIRF` = `Menu`.`Categ`
  WHERE `Categ`.`SIRF` = 2
  GROUP BY `LOGICDATE`

In any case, the data in sqlfiddle for `Categ`.`SIRF` = 2 will result in an empty result.
(`Categ`.`SIRF` = 2) => (`Menu`.`SIRF` = 4) => no lines from `ARcheck`