SQL Order

[renee35]

I need some help with trying to make these table joins work. What I have is list of joins, but the order I need the joins to take place is generating an error: "The column prefix 's' does not match with a table name or alias name used in the query." Here are my table joins:

from rmxprod.dbo.candidates c
INNER JOIN rmxprod.dbo.vw_ordercandidates_all oc on oc.candidateid = c.candidateid
INNER JOIN rmxprod.dbo.orders o on o.orderid = oc.orderid
LEFT OUTER JOIN rmxprod.dbo.medicalplans m on m.medicalplandescription = s.reference
LEFT OUTER JOIN rmxprod.dbo.medicalplancosts mc on mc.medicalplanid = m.medicalplanid and mc.clientid = o.clientid
LEFT OUTER JOIN rmxprod.dbo.medicalplancosts mpc on mpc.clientid = -1 and mpc.medicalplanid = m.medicalplanid
LEFT OUTER JOIN integrateprod.dbo.intxref i on i.extid = oc.candidateid and fieldid = 186 and i.subscriberid = 1
LEFT OUTER JOIN #staffserve s on s.intid = i.intid


I need to join medicalplans first, but I cannot figure out how or should I say is it possible to do? I know the reason I am getting the error is because table "s" is not brought into the joins until the end?

Any help provided will be greatly appreciated.

Thanks a bunch!!

-T

 

[mrdenny]

You have to move #staffserve higher up. You can't reference a table until it's been added to join.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[xlbo]

Please post in a an appropriate forum - this has nothing to do with Reporting Services

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question