Subquery to verify is 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

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery to verify is record exist in other table

Author

Topic

karla0612
Starting Member

5 Posts

Posted - 2009-07-22 : 15:48:36

Hi,

I'm writing a Query to create a file with data of some record. I get this data from different rows.
I need to include some code that verify in that record exist in other table named SUBSCRIBERCOB and if it exist write in the file TRUE or FALSE, in the same row with all the other data of the record.

Please help me, I don't know if I can use CASE and If exist.

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'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:11:44

use a LEFT OUTER JOIN to SUBSCRIBERCOB.

Then a case statement

WHEN xxx is NULL then 'FALSE' ELSE TRUE End as recordExistsInOtherTble

I'd give you the code, but you didn't show us what the SUBSCRIBERCOB table looks like

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources