Problem joining 3 tables

[mluken]

I am having a problem joining three tables together in this query. Everything looks fine to me, but I am getting an error when adding 'T5' into the mix. The following query DOES work:

SELECT DISTINCT  
      T1.USR_ID,  
      T1.USR_NM,  
      T1.FRST_NM,  
      T1.LST_NM,  
      T1.USR_CITY,  
      T4.ACNT_ID,  
      T4.MBRSHP_ID
FROM  
      DB2_APPNAME_USRS T1 LEFT JOIN  
      (    
            SELECT     
                  USR_ID, 
                  ACNT_ID,      
                  MBRSHP_ID    
            FROM      
                  DB2_APPNAME_USR_DTL    
            WHERE      
                  USR_ID = 1879487720  
      ) T4 ON T1.USR_ID = T4.USR_ID
WHERE  
      T1.USR_ID =  1879487720

However, the following does NOT work:

SELECT DISTINCT  
      T1.USR_ID,  
      T1.USR_NM,  
      T1.FRST_NM,  
      T1.LST_NM,  
      T1.USR_CITY,  
      T4.ACNT_ID,  
      T4.MBRSHP_ID,  
      [b]T5.CC_EMAIL_FLG,  
      T5.ALLOW_CNTC_FLG[/b]  
FROM  
      [b]DB2_APPNAME_ACNT_SETTINGS T5,[/b]
      DB2_APPNAME_USRS T1 LEFT JOIN  
      (    
            SELECT     
                  USR_ID, 
                  ACNT_ID,      
                  MBRSHP_ID      
            FROM      
                  DB2_APPNAME_USR_DTL    
            WHERE      
                 USR_ID = 1879487720  
      ) T4 ON T1.USR_ID = T4.USR_ID
WHERE  
      T1.USR_ID =  1879487720 [b]AND
      T1.USR_ID = T5.USR_ID[/b]

I have eliminated the obvious problems. The table T5 is spelled correctly, and the T5 columns that I call are listed correctly. The error message I get is:

[Microsoft][ODBC Microsoft Access Driver] Join expression not supported.

Any input I could get would be greatly appreciated. I have been scratching my head over this all day!

 

[donutman]

FROM  
      DB2_APPNAME_ACNT_SETTINGS T5 [Blue]INNER[/Blue] [Blue]JOIN[/Blue]
         [Blue]ON[/Blue] [Gray]([/Gray]T1.USR_ID [Gray]=[/Gray] T5.USR_ID[Gray])[/Gray]
      DB2_APPNAME_USRS T1 [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue]

and remove it from the Where clause.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

http://www.cnn.com/2004/SHOWBIZ/TV/11/08/television.sixfeet.reut/index.html

Just testing...ignore this post.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]