Jump to content

JOIN 3 table then make WHERE


Go to solution Solved by lilmer,

Recommended Posts

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 by lilmer
Link to comment
https://forums.phpfreaks.com/topic/280273-join-3-table-then-make-where/
Share on other sites

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,

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. . 

  • Solution


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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.