N K N K - 6 months ago 9
SQL Question

Incorrect syntax near the keyword 'join'

My below query is causing an error:


Incorrect syntax near the keyword 'join'.


Select top 1
a.mkey, emp_received, a.mkey,
c.type_desc DOC_TYPE, a.doc_no INWARD_NO,
c.type_desc dept_received, c.type_desc EMP_RECEIVED,
convert(varchar, a.doc_date, 103) date,
a.to_user, a.No_of_pages, Ref_No, c.type_desc,
e.emp_name NAME,
ISNULL(e.Email_Id_Official, 'abc@test.com') EMAILID
from
inward_doc_tracking_hdr a
left join
type_mst_a c on a.doc_type = c.master_mkey
and
left join
type_mst_a c on a.dept_received = c.master_mkey
left join
emp_mst e on a.emp_received = e.mkey
where
a.emp_received is not null
and a.mkey = (select mkey
from inward_doc_tracking_hdr aa
where doc_no='IW/16/97')


I don't know why it is causing that error

As per gordon's answer, I added one more left join for emp_received, but it is not working

Select top 1 a.mkey,
cd.type_desc as DOC_TYPE, a.doc_no as INWARD_NO,
cr.type_desc as DEPT_RECEIVED, qr.type_desc as EMP_RECEIVED,
convert(varchar(255), a.doc_date,103) as date,
a.to_user, a.No_of_pages, Ref_No,
e.emp_name as NAME,
coalesce(e.Email_Id_Official, 'test@test.com') EMAILID
from inward_doc_tracking_hdr a left join
type_mst_a cd
on a.doc_type = cd.master_mkey left join
type_mst_a cr
on a.emp_received = qr.master_mkey left join
type_mst_a st
on a.dept_received = cr.master_mkey left join
emp_mst e
on a.emp_received = e.mkey
where a.emp_received is not null and
a.mkey = (select mkey
from inward_doc_tracking_hdr aa
where doc_no = 'IW/16/97'
);

Answer

I think the query you want is more like this:

Select top 1 a.mkey,emp_received , 
       a.mkey, cd.type_desc as DOC_TYPE,  a.doc_no as INWARD_NO,
       cr.type_desc as dept_received, cr.type_desc as EMP_RECEIVED,   
       convert(varchar(255), a.doc_date,103) as date,
       a.to_user, a.No_of_pages, Ref_No, 
       e.emp_name as NAME, 
       coalesce(e.Email_Id_Official, 'test@test.com') as EMAILID   
from inward_doc_tracking_hdr a left join
     type_mst_a cd
     on a.doc_type = cd.master_mkey left join
     type_mst_a cr
     on a.dept_received = cr.master_mkey left join
     emp_mst e
     on a.emp_received = e.mkey 
where a.emp_received is not null and 
      a.mkey = (select mkey
                from inward_doc_tracking_hdr aa
                where doc_no = 'IW/16/97'
               );

Notes:

  • The immediate problem is and left join. The and causes a syntax error.
  • You have two tables with the same alias. That is not allowed.
  • You need to be careful about which columns come from which tables; I made a good guess.
  • Whenever you use varchar() in SQL Server, always include a length. The default length varies by context, and if you are lazy, you will probably just suffer a hard-to-debug error at some point.
  • Use as when defining column aliases.
Comments