how to convert this to join without using IN

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)

how to convert this to join without using IN

Author

Topic

neha.mangla
Starting Member

1 Post

Posted - 2007-06-25 : 10:33:00

SELECT *
FROM sdp_subscriptionservicevariant
WHERE subscription_servicevariant_id IN (
SELECT subscription_servicevariant_id
FROM lnk_suboffer_subsvcvariant
WHERE subscription_offer_id =
in_subscriptionofferid)
AND status_id NOT IN (SELECT status_id
FROM ref_status_type
WHERE status_name = 'inactive');

neha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 10:49:05

try this

SELECT *
FROM sdp_subscriptionservicevariant a 
INNER JOIN lnk_suboffer_subsvcvariant b
ON a.subscription_servicevariant_id = b.subscription_servicevariant_id
LEFT JOIN ref_status_type c
ON  a.status_id = c.status_id
AND c.status_name = 'inactive'
WHERE c.status_id IS NULL

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources