Avatar of Patrick Wrigley
Patrick Wrigley
Flag for United States of America asked on

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

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 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')
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Patrick Wrigley

8/22/2022 - Mon
Vitor Montalvão

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') 

Open in new window

Vitor Montalvão

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')

Open in new window

Patrick Wrigley

ASKER
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')
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Wrigley

ASKER
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'
Ryan Chong


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?
Vitor Montalvão

getting closer now I get
Which solution are you using? Mine or Ryan's?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Wrigley

ASKER
trying both atm
Patrick Wrigley

ASKER
fixed it thank you
Vitor Montalvão

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')

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ryan Chong

@PatWrigley

you may post your working version here
Patrick Wrigley

ASKER
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'.
Vitor Montalvão

I would be surprised if it was working since you didn't provide the column names of the tables.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ryan Chong

@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.
Patrick Wrigley

ASKER
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')
Ryan Chong

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') 

Open 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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vitor Montalvão

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.
Patrick Wrigley

ASKER
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!