SQL Query incorrect syntax near the keyword ON any help would be appreciated

in the last SELECT, isn't ON but WHERE:

select DISTINCT
        [PTID]                                          
        ,[EMPLOYER]                                            
FROM MREL99 emp 
WHERE (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer') 

Select allOpen in new window

Oh, I can see now by the alias that after all what was missing was an INNER JOIN with other table:

select DISTINCT
        [PTID]                                          
        ,[EMPLOYER]                                            
FROM MREL99 emp 
    INNER JOIN MRLI99 I99 ON (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer')

Select allOpen in new window

OK using the code below I am getting
The multi-part identifier "I99.PTID" could not be bound.
The multi-part identifier "I99.D_CLIN_LIST" could not be bound.
The multi-part identifier "I99.T_CLIN_LIST" could not be bound.
Invalid column name 'EMPLOYER'.


select top 100 PVID, PTID, *
from MRTX99
where PTID = 6184

SELECT  top 100 *
FROM MRLI99 I99
where PTID = 6184

select DISTINCT
        [PTID]                                          
        ,[EMPLOYER]                                            
FROM MREL99 emp
WHERE (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer')

getting closer now I get


Invalid column name 'D_CLIN_LIST'.
Invalid column name 'T_CLIN_LIST'.
Ambiguous column name 'PTID'.
Invalid column name 'EMPLOYER'

Invalid column name 'D_CLIN_LIST'.
Invalid column name 'T_CLIN_LIST'.
Ambiguous column name 'PTID'.
Invalid column name 'EMPLOYER'

can you tell us in which tables these fields are coming from?

getting closer now I get

Which solution are you using? Mine or Ryan's?

trying both atm

fixed it thank you

For the ambiguous name error you must add the correct alias. For the other error you need to check if the column names are corrected written:

select DISTINCT
        emp.[PTID]                                          
        ,I99.[EMPLOYER]                                            
FROM MREL99 emp 
    INNER JOIN MRLI99 I99 ON (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer')

Select allOpen in new window

@PatWrigley

you may post your working version here

actually it isnt working.  I am now useing

select top 100 PVID, PTID, *
from MRTX99
where PTID = 6184

SELECT  top 100 *
FROM MRLI99 I99
where PTID = 6184
select DISTINCT
        emp.[PTID]                                          
        ,I99.[EMPLOYER]                                            
FROM MREL99 emp
    INNER JOIN MRLI99 I99 ON (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer')
 and getting

Invalid column name 'D_CLIN_LIST'.
Invalid column name 'T_CLIN_LIST'.
Invalid column name 'EMPLOYER'.

I would be surprised if it was working since you didn't provide the column names of the tables.

@PatWrigley

Invalid column name 'D_CLIN_LIST'.
Invalid column name 'T_CLIN_LIST'.
Invalid column name 'EMPLOYER'.

check where these fields are coming from which tables? post it here so we can advise proper solution to you.

now using this and getting syntax error near equal sign

select top 100 *
from MRTX99
where PTID = 6184


-- patient info
SELECT  top 100 *
FROM MRLI99
where PTID = 6184



-- employer

select DISTINCT
       [PTID]                                          
       ,[EMPLOYER]                                    
      
FROM
MREL99 emp INNER JOIN MRCI99 (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer')

there is syntax error near:

MREL99 emp INNER JOIN MRCI99 (emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST AND emp.ELEMENT_NAME = 'Employer') 

Select allOpen in new window

are you following the syntax as showing for the accepted "answer" above? (comment: ID: 42081957)

and you still not mentioned where are those fields are coming from, which make us difficult to assist you.

are you following the syntax as showing for the accepted "answer" above? (comment: ID: 42081957)

Looks like OP accepted one solution but using other. A little bit confusing, yes.

This went completely sideways on me.  I was trying to help a pal overseas with the SQL query.  He has since abandoned the issue and I have no way of getting anymore info on his issue.  It  has turned into a complete mess of a question.  Thank you everyone for your willingness to help!