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

Patrick Wrigley
Patrick Wrigley used Ask the Experts™
on
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')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

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')
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Software Team Lead
Commented:
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

Author

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'
Ryan ChongSoftware Team Lead

Commented:

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ãoIT Engineer
Distinguished Expert 2017

Commented:
getting closer now I get
Which solution are you using? Mine or Ryan's?

Author

Commented:
trying both atm

Author

Commented:
fixed it thank you
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Ryan ChongSoftware Team Lead

Commented:
@PatWrigley

you may post your working version here

Author

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'.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I would be surprised if it was working since you didn't provide the column names of the tables.
Ryan ChongSoftware Team Lead

Commented:
@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.

Author

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')
Ryan ChongSoftware Team Lead

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial