August 19, 2011 at 2:33 pm
Here is my FROM statement:
FROM ( CovElig as c
LEFT JOIN MT_BenPlans as bp
ON c.GrpName = bp.PlanCdStd)
LEFT JOIN MT_CurMo AS cm
ON UCase(c.Gen_mem) = cm.CurrrentMonthField
WHERE bp.AdminCd = "COV"
AND bp.BenCd = "MED";
my delimea is this c.GrpName is a text field that is not numbers. I will need to link to the numbers in bp.PlanCdStd based on the text that is in c.GrpName. For instance just an example (I'd have to look up what the actual codes are and the actual text the correspond to)
c.GrpName="BlueCross","001"
c.GrpName="Coventry","006"
then those 3 digit codes would correspond to the 3 digit code in the bp.PlanCdStd
is it possible for me to make a link like that?
August 19, 2011 at 3:06 pm
You can only join tables on common elements. GroupName (Generic Name) to Group Name or GroupCode to Group Code.
The database should be storing the code in the child table so that you can link by code..
I have inherited databases that are structured the way you database is I would alter the structure to add the GroupCode Column in the child table and perform an update by joining on name to populate the GroupCode Table.
It appears that you have some other design issues that could be changed so that it is easier to write your queries.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 19, 2011 at 4:46 pm
Can you provide sample structure and data? Maybe there is something there between them that can be used.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply