Select JOIN 3 Table

[lilmer]

I've got 3 tables on my database.

verifier = verifierId = email = verifyAddressdocs = docId = verifierIddocFile = fileId = fileDesc = fileName = fileStatus = docId

there's a two data on docFile with the same docId. .

On joining all I have to do is this:

Select * FROM verifier as v JOIN docs as d ON v.verifierId = d.verifierId JOIN docfiles as f ON d.docId=f.docId

It will show this:



But what I really want to happen is, on my PHP side. . I want to check the fileStatus of the file. . I will make a condition like. .

if($fileStatus == 0 && $fileStatus == 0){ echo 'Pending';}elseif($fileStatus == 1 && $fileStatus == 1){ echo 'Approve';}elseif($fileStatus == 1 && $fileStatus == 0){ echo 'On going';}elseif($fileStatus == 0 && $fileStatus == 1){ echo 'On going';}

Or Maybe if you can give me a better table structure for this. . Thanks Edited April 26, 2013 by lilmer

[Barand]

I think I've deciphered the table structure

verifier
    verifierId
    email
    verifyAddress
docs
    docId
    verifierId
docFile
    fileId
    fileDesc
    fileName
    fileStatus
    docId

But I cannot understand how fileStatus can be both 0 and 1 at the same time below

if ($fileStatus == 0 && $fileStatus == 0) { 
    echo 'Pending';
}
elseif ($fileStatus == 1 && $fileStatus == 1) { 
    echo 'Approve';
}
elseif ($fileStatus == 1 && $fileStatus == 0) { 
    echo 'On going';
}
elseif ($fileStatus == 0 && $fileStatus == 1) { 
    echo 'On going';
}

[Jessica]

There's two docs, he needs to alias the columns.

[Barand]

Aliasing the columns would only be a solution if the status values were in different tables. I think the answer is to check if they are all 0, all 1 or a mix for each docid. That way it works for 2 docfiles or 20 docfiles.

Something like this

SELECT verifierId, docId,
    CASE 
        WHEN tot0 = totdocs THEN 'Pending'
        WHEN tot1 = totdocs THEN 'Approved'
        ELSE 'Ongoing'
    END as status
FROM (
    SELECT v.verifierId, d.docId,
        SUM(IF(f.fileStatus=0,1,0)) as tot0,
        SUM(IF(f.fileStatus=1,1,0)) as tot1,
        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
    ) as tots

Edited April 27, 2013 by Barand

[lilmer]

WOw, thanks for the answer and for the knowledge. . you so much Barand, don't know much bout SQL commands. .