See if you can solve this brain teaser

[skwon9642]

I have been struggling with this program for a long time. I have been getting much help from you guys, so thank you. I believe this is the final question for this program (hopefully).
Attached is my crappy diagram of the process.

1. get number from WrkRank (1, 2 or 3) and locate the CCSDSC
2. get CCDESC that matches the CCSDSC
3. find matching EMDID from EDMAST
4. locate corresponding EMFAST
5. find matching MBMSTS from EDMTAB
6. get corresponding MBCODE

the step I am missing is, "if there are any MBCODE that matches CCSDSC and CCTABL = 'STSCONTB' from ZCC, do not include. (in red)

SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT          
FROM EDI4XXDTA/EDMAST as EDMAST                    
INNER JOIN ZCC AS ZCC                              
ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3))      
INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB              
ON EDMAST.EMFAST = EDMTAB.MBMSTS                   
WHERE (CAST(ZCC.CCSDSC AS Char(2)) = :Wrkrank AND        
       ZCC.CCTABL = 'DOCCONTB')                 
GROUP BY EMDID, EMBOX, MBCODE, EMDAT                                                                  
ORDER BY EMDID, EMBOX, MBCODE, EMDAT

The problem is that if I want to include the new condition, I have to put another condition in the where clause, BUT since I have to say CCTABL = 'STSCONTB' in the same cluase as CCTABL = 'DOCCONTB', it returns nothing because there are no records that can be both at the same time. I tried OR instead of AND but that is no good because I need it to fulfill both conditions not one OR the other. I have tried a HAVING method after doing some search, but that doesn't work either. I also heard you can create a whole new table that I can make comparisons to within the query but no luck finding anything online for this method.

Here is the HAVING method that I tried. returns nothing.

SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT          
FROM EDI4XXDTA/EDMAST as EDMAST                    
INNER JOIN ZCC AS ZCC                              
ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3))      
INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB              
ON EDMAST.EMFAST = EDMTAB.MBMSTS                   
WHERE (CAST(ZCC.CCSDSC AS Char(2)) = WrkRank AND        
       ZCC.CCTABL = 'DOCCONTB') OR                 
      (EDMTAB.MBCODE NOT IN                        
      (SELECT CAST(CCSDSC AS CHAR(3)) FROM ZCC) AND
       ZCC.CCTABL = 'STSCONTB')                    
GROUP BY EMDID, EMBOX, MBCODE, EMDAT               
HAVING COUNT(DISTINCT ZCC.CCTABL) = 2              
ORDER BY EMDID, EMBOX, MBCODE, EMDAT

I did not include EMBOX and EMDAT as they would just follow the EMDID if the correct ones are returned. They are in the EDMAST table.

I hope I made it clear. If not PLEASE let me know so i can clarify...I need help TT.TT Thank you.

[Brian Rusch]

Here's a stab at it, change the WHERE clause in your first example to:

WHERE CAST(ZCC.CCSDSC AS Char(2)) = :Wrkrank AND ZCC.CCTABL = 'DOCCONTB'
AND EDMTAB.MBCODE NOT IN (SELECT CAST(A.CCSDSC AS CHAR(3)) FROM ZCC A WHERE A.CCTABL = 'STSCONTB')

[skwon9642]

Here's a stab at it, change the WHERE clause in your first example to:

WHERE CAST(ZCC.CCSDSC AS Char(2)) = :Wrkrank AND ZCC.CCTABL = 'DOCCONTB'
AND EDMTAB.MBCODE NOT IN (SELECT CAST(A.CCSDSC AS CHAR(3)) FROM ZCC A WHERE A.CCTABL = 'STSCONTB')

Thank you SQL master.