Subquery to verify if record exist in other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery to verify if record exist in other table

Author

Topic

karla0612
Starting Member

5 Posts

Posted - 2009-07-22 : 15:49:32

SELECT
CASE WHEN S.subscriber_dep_num_str_id = '00' THEN
'0'
WHEN S.subscriber_dep_num_str_id BETWEEN '20' AND '49' THEN
'1'
Else
'2'
END,
S.social_security_num_str,
dbo.fn_MA_Pad_String(CL.first_name_str,25),
dbo.fn_MA_Pad_String(CL.second_name_str,1),
dbo.fn_MA_Pad_String(CL.first_last_name_str,25),
dbo.fn_MA_Pad_String(ltrim(rtrim(CA.address1_str)) + ' ' + ltrim(CA.address2_str),60),
dbo.fn_MA_Pad_String(CA.city_str,15),
dbo.fn_MA_Pad_String(CA.state_str_id,4),
dbo.fn_MA_Pad_String(CA.zip_code1_str_id,6),
dbo.fn_MA_Pad_String(S.policy_type_num_id,2),
dbo.fn_MA_Pad_String(CT.contract_type_str_id,3),
convert(varchar(10), S.premium_effective_date, 111),
convert(varchar(10), S.birth_date, 111),
CASE WHEN S.coverages_str like '%C%'THEN
'1'
Else
'0'
END AS Medicare
FROM Subscriber S
INNER JOIN Client CL ON
S.subscriber_identity_num_id = CL.subscriber_identity_num_id
INNER JOIN DependentCode DC ON
DC.subscriber_dep_num_str_id = S.dep_rel_code_str_id
INNER JOIN Contract C ON
C.contract_client_num_str_id = S.contract_client_num_str_id
INNER JOIN ContractType CT ON
C.contract_type_str_id = CT.contract_type_str_id
INNER JOIN SubscriberAddress SA ON
S.subscriber_identity_num_id = SA.subscriber_identity_num_id
INNER JOIN ClientAddress CA ON
CA.sequence_num = SA.sequence_num
WHERE S.group_code_str = '115090' AND S.status_code_str_id = '1' AND CA.address_location_num_id = '1'

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-22 : 15:56:45

is there a question?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:13:30

is a duplicate post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129844

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources