SQL Query with INNER and LEFT JOIN NOT Working

[VAST39]

Hi guys,

I am writing a query that will join my addr table to the main query and display everyone's address. The problem is several people do not have an address record so I want to do a LEFT JOIN and have them show up in the query anyways.

I keep getting syntax errors when I try to add in the LEFT JOIN. Can someone tell me what is wrong with this query structure? Thanks in advance.

SELECT   A.MBR_SSN_NBR, 
         A.MBR_TOT_CNTRB_AMT, 
         C.MBR_F_NM, 
         C.MBR_L_NM, 
         C.MARITAL_STATUS
         
          
FROM     DSNP.PR01_T_MBR_SYS A 
          
         
INNER JOIN  DSNP.PR01_T_MBR C ON A.MBR_SSN_NBR=C.MBR_SSN_NBR 
and 
LEFT JOIN DSNP.PR01_T_EMPR_ADDR D on A.MBR_SSN_NBR = D.MBR_SSN_NBR


AND      A.MBR_STAT_CD = '1' 
AND      A.MBR_TOT_CNTRB_AMT > 0.00 
AND      NOT EXISTS    (SELECT 1 FROM DSNP.PR01_T_BENEF b   WHERE B.MBR_SSN_NBR = A.MBR_SSN_NBR)

Note: I have tried it with and without the AND before the LEFT JOIN and it doesn't work. I guess I don't know how to mix INNER and LEFT JOINS in the same query.

 

[jbenson001]

Remove the "and" between the join statements.
The rest looks ok.
then post back with the syntax error you are getting if you get any.

 

[imex]

Hi,

I believe it is also necessary to replace the And that is after the Left Join by a Where:

WHERE    A.MBR_STAT_CD = '1'

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[jbenson001]

The where is not necessary, you can have ANDs inside of a join. But with out the poster posting the syntax error he/she is getting, it is guessing at this poing.

 

[VAST39]

You can disregard this one, guys. I found a solution by putting the main query in brackets and making a sub-query and then doing the LEFT JOIN in the outer query.