....
FROM 
HEALTH_ORGANISATIONS 
INNER JOIN SCHEDULES AS SCP  ON some condition missing here 
INNER JOIN REFERENCE_VALUES AS ATTND ON SCP.ATTND_REFNO = ATTND.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS CONTP ON SCP.CONTP_REFNO = CONTP.RFVAL_REFNO 
INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO
INNER JOIN REFERENCE_VALUES AS SEXXX ON PATIENTS.SEXXX_REFNO = SEXXX.RFVAL_REFNO
INNER JOIN PROF_CARERS AS HCPs 
ON SCP.PROCA_REFNO = HCPs.PROCA_REFNO  
INNER JOIN REFERENCE_VALUES AS PRTYP 
ON HCPs.PRTYP_REFNO = PRTYP.RFVAL_REFNO

quote:

---

Originally posted by satya068

The same thing i have done for my other script,which i got the expected results..

FROM
HEALTH_ORGANISATIONS INNER JOIN SCHEDULES AS SCP
INNER JOIN REFERENCE_VALUES AS VISIT ON SCP.VISIT_REFNO = VISIT.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS ATTND ON SCP.ATTND_REFNO = ATTND.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS CONTY ON SCP.CONTY_REFNO = CONTY.RFVAL_REFNO
INNER JOIN REFERRALS ON SCP.REFRL_REFNO = REFERRALS.REFRL_REFNO
INNER JOIN REFERENCE_VALUES AS REASN ON REFERRALS.REASN_REFNO = REASN.RFVAL_REFNO
INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO
INNER JOIN REFERENCE_VALUES AS SEXXX ON PATIENTS.SEXXX_REFNO = SEXXX.RFVAL_REFNO
LEFT JOIN SPECIALTIES AS SPECIALTIES_1 ON REFERRALS.REFTO_SPECT_REFNO = SPECIALTIES_1.SPECT_REFNO
LEFT JOIN STAFF_TEAMS ON REFERRALS.REFTO_STEAM_REFNO = STAFF_TEAMS.STEAM_REFNO
INNER JOIN REFERENCE_VALUES AS SORRF ON REFERRALS.SORRF_REFNO = SORRF.RFVAL_REFNO
--LEFT JOIN REFERENCE_VALUES ON PC_REF.PRTYP_REFNO = REFERENCE_VALUES.RFVAL_REFNO
INNER JOIN PROF_CARERS AS HCPs INNER JOIN REFERENCE_VALUES AS PRTYP ON HCPs.PRTYP_REFNO = PRTYP.RFVAL_REFNO ON SCP.PROCA_REFNO = HCPs.PROCA_REFNO
INNER JOIN SPECIALTIES ON SCP.SPECT_REFNO = SPECIALTIES.SPECT_REFNO
INNER JOIN SERVICE_POINTS ON SCP.SPONT_REFNO = SERVICE_POINTS.SPONT_REFNO ON HEALTH_ORGANISATIONS.HEORG_REFNO = SERVICE_POINTS.HEORG_REFNO
LEFT JOIN PROF_CARER_EPISODES INNER JOIN REFERENCE_VALUES ON PROF_CARER_EPISODES.REFRL_REFNO = REFERENCE_VALUES.RFVAL_REFNO ON PROF_CARER_EPISODES.PATNT_REFNO = REFERRALS.PATNT_REFNO

---

quote:

---

Originally posted by Pom Grewal

Hi Forum

Im working on a an update to two table that are joined. The select query works fine and gives my the desired results, however when I try and run the update, I get an "Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't1'." error message. Below is my select and update queries. i hope someone can help me.

select t1.[Document No_],
t1.[No_],
t1.[Posting Type],
t2.[Order Date],
t1.[Posting Type],
t1.[Business with Name],
t1.[Business-with No_],
t2.[Task-at Name],
t1.[Amount],*
from DHL.dbo.[DHL Waste$Waste Management Line] t1
left join DHL.dbo.[DHL Waste$Waste Management Header] t2
on t2.[No_] = t1.[Document No_]
where t2.[Business-with Name] like 'DHL Diageo%'
and t1.[Business-with No_] like 'CU-000465'
and t2.[Task-at Name] like 'DHL Diageo%'
and t1.[Sub Contract No_] = '07'



update DHL.dbo.[DHL Waste$Waste Management Line] t1
left join DHL.dbo.[DHL Waste$Waste Management Header] t2
on t2.[No_] = t1.[Document No_]
set t2.[Business-with No_] = 'CU-000573',
t2.[Business-with Name] = 'DHL Diageo - Pepsico',
t2.[Task-at Name] = 'DHL Diageo - Pepsico',
t1.[Sub Contract No_] = '01',
t1.[Business-with No_] = 'CU-000573',
--t1.[Business-with Name] = 'DHL Diageo - Pepsico',
--t1.[Post-with No_] = 'CU-000573',
--t1.[Invoice-with No_] = 'CU-000573'
t1.[Contract No_] = 'WC-000573'
where t2.[Business-with Name] like 'DHL Diageo%'
and t1.[Business-with No_] like 'CU-000465'
and t2.[Task-at Name] like 'DHL Diageo%'
and t1.[Sub Contract No_] = '07'

---