JOIN 3 table then make WHERE

[lilmer]

SELECT verifierId, email, notes, docId, 
    CASE
        WHEN tot0 = totdocs THEN 'Pending'
        WHEN tot1 = totdocs THEN 'Approved'
        WHEN tot2 = totdocs THEN 'Declined'
        WHEN tot3 = totdocs THEN 'Incomplete'
        ELSE 'Ongoing'
    END as status
FROM (
    SELECT v.verifierId, v.email , v.notes, d.docId,
        SUM(IF(f.fileStatus=2,1,2)) as tot2,
        SUM(IF(f.fileStatus=0,1,0)) as tot0,
        SUM(IF(f.fileStatus=1,1,0)) as tot1,
        SUM(IF(f.fileStatus=0,2,1)) as tot3,
        COUNT(f.fileId) as totdocs
      
        FROM verifier as v
        JOIN docs as d ON v.verifierId = d.verifierId
        JOIN docfiles as f ON d.docId=f.docId
        GROUP BY v.verifierId, d.docId
        ORDER BY v.date DESC
        ) as tots

-----[by the help OF BARAND]---- 

this is query and its working fine, when I add

WHERE totdocs = tot2
.... or
WHERE totodocs = tot1

its fine and I still get what  I need, BUT  when it comes to this:

WHERE totdocs = tot3

it shows all the record, all I want is the "INCOMPLETE" record only. . 

 

By the way, using tot3 and the CASE WHEN it is fine to show the "INCOMPLETE" record, it is when I make a "WHERE totdocs = tot3" statement I got a problem. . 

 

Anyone can explain to me why?

Edited July 18, 2013 by lilmer

[Barand]

the purpose of these lines

SUM(IF(f.fileStatus=0,1,0)) as tot0,
SUM(IF(f.fileStatus=1,1,0)) as tot1,

is to count the number of rows with fileStatus 0 and 1.

 

I do not understand what your additions do ie

        SUM(IF(f.fileStatus=2,1,2)) as tot2,
        SUM(IF(f.fileStatus=0,2,1)) as tot3,

[lilmer]

SUM(IF(f.fileStatus=2,1,2)) as tot2,
SUM(IF(f.fileStatus=0,2,1)) as tot3,

2,1,2 to get the status records that both of them are 2. . 

 

and the  0,2,1 is to get the records with fileStatus with 2 or 1, the first record is 2, and the other one is 1 vice versa. . 

 

actually they are working fine when  I just Select them without using the Where = tot3 . . 

 

I'm having a trouble when I want only to select he records with both 2 or 1 file status. . 

[lilmer]

SELECT main.* FROM (

   SELECT tots.verifierId, tots.email,tots.notes, tots.docId,

        CASE

                WHEN tot0 = totdocs THEN 'Pending'

                WHEN tot1 = totdocs THEN 'Approved'

                WHEN tot2 = totdocs THEN 'Declined'

                WHEN tot3 = totdocs THEN 'Incomplete'

                ELSE 'Ongoing'

        END as docstatus



        FROM (

                SELECT v.verifierId, v.email , v.notes, d.docId,

                    SUM(IF(f.fileStatus=2,1,2)) as tot2,

                    SUM(IF(f.fileStatus=0,1,0)) as tot0,

                    SUM(IF(f.fileStatus=1,1,0)) as tot1,

                    SUM(IF(f.fileStatus=0,2,1)) as tot3,

                    COUNT(f.fileId) as totdocs

                FROM verifier as v

                    JOIN docs as d ON v.verifierId = d.verifierId

                    JOIN docfiles as f ON d.docId=f.docId

                    GROUP BY v.verifierId, d.docId

                    ORDER BY v.date DESC

            ) as tots



  ) as main

WHERE main.docstatus = 'Incomplete'

 

TASK RESOLVE