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')
Patrick WrigleyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Patrick WrigleyAuthor Commented:
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')
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it seems that table: MRTX99 was not being used in your query?

are you using cte?

if yes, try customize this:

with I99 as
(
	SELECT  top 100 *
	FROM MRLI99
	where PTID = 6184
)
select DISTINCT
       [PTID]                                          
       ,[EMPLOYER]                                    
FROM MREL99 emp 
inner join I99
ON emp.PTID = I99.PTID AND emp.D_ELEMENT = I99.D_CLIN_LIST AND emp.T_ELEMENT = I99.T_CLIN_LIST
Where emp.ELEMENT_NAME = 'Employer'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick WrigleyAuthor Commented:
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'
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:

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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
getting closer now I get
Which solution are you using? Mine or Ryan's?
0
Patrick WrigleyAuthor Commented:
trying both atm
0
Patrick WrigleyAuthor Commented:
fixed it thank you
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@PatWrigley

you may post your working version here
0
Patrick WrigleyAuthor Commented:
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'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would be surprised if it was working since you didn't provide the column names of the tables.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@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.
0
Patrick WrigleyAuthor Commented:
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')
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Patrick WrigleyAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.