August 27, 2005 at 6:46 pm
Hi guys,
I'm a newbie at this and I'm trying to put together a query, if anyone can help I'd really appreciate it. The first part of my query is as such:
SELECT DISTINCT
TBL.PartnerID AS 'Value'
, TBL.Partner AS 'Name'
FROM dbo.tblPartner TBL (NOLOCK)
JOIN dbo.tblUser USR
ON TBL.PartnerID = USR.PartnerID
WHERE USR.AccessLevelID = 1
AND USR.PassportID IS NOT NULL
But I want to continue with another "AND" as such:
AND
(SELECT COUNT(CAMPAIGN.PartnerID)
FROM dbo.tblCampaign as CAMPAIGN (NOLOCK)
WHERE CAMPAIGN.PartnerID = TBL.PartnerID) > 0
Can anyone can show me how to combine the two to follow this logic:
SELECT DISTINCT
TBL.PartnerID AS 'Value'
, TBL.Partner AS 'Name'
FROM dbo.tblPartner TBL (NOLOCK)
JOIN dbo.tblUser USR
ON TBL.PartnerID = USR.PartnerID
WHERE USR.AccessLevelID = 1
AND USR.PassportID IS NOT NULL
AND
(SELECT COUNT(CAMPAIGN.PartnerID)
FROM dbo.tblCampaign as CAMPAIGN (NOLOCK)
WHERE CAMPAIGN.PartnerID = TBL.PartnerID) > 0
Any help would be great and much appreciated!
August 28, 2005 at 7:38 am
Why not join to the campaign table? You needn't return any data from it (same as you are using the USR table but not returning data). The distinct clause will ensure that even if there are several campaigns for the one partner, only one will be returned.
Alternatively, use exists, as in....
SELECT DISTINCT
TBL.PartnerID AS 'Value'
, TBL.Partner AS 'Name'
FROM dbo.tblPartner TBL (NOLOCK)
JOIN dbo.tblUser USR
ON TBL.PartnerID = USR.PartnerID
WHERE USR.AccessLevelID = 1
AND USR.PassportID IS NOT NULL
AND exists (select * from dbo.tblCampaign C
where C.partnerID = TBL.partnerID)
This will perform very efficiently - if it finds even a single row that exists for the criteria, it won't bother continuing scanning. It meets the requirements for what you wanted without having to count rows from the campaign table, etc.
Hope this helps. Try out each way - you can use "Show Execution Plan" under the query menu in query analyser to show you what SQL is doing under the hood to let you work out which way is better.
Make sure you have indices on the foreign keys (eg, partnerID in the campaign table) to help SQL evaluate the exists or join efficiently.
Cheers,
Ian
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply