We help IT Professionals succeed at work.

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

110 Views
Last Modified: 2017-04-07
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

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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

Patrick WrigleyNetwork Manager

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')
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Patrick WrigleyNetwork Manager

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'
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
trying both atm
Patrick WrigleyNetwork Manager

Author

Commented:
fixed it thank you
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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

CERTIFIED EXPERT

Commented:
@PatWrigley

you may post your working version here
Patrick WrigleyNetwork Manager

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
CERTIFIED EXPERT
Distinguished Expert 2017

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

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.
Patrick WrigleyNetwork Manager

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

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
CERTIFIED EXPERT
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.
Patrick WrigleyNetwork Manager

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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions