August 27, 2005 at 6:47 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 3:08 am
So you want to display the campaign count for the partner on the same row? This is untested, but along the right lines - you will see that I have created a subquery returning partnerid's and counts and joined that to your tblPartner table to return the required count. Good luck.
SELECT DISTINCT
TBL.PartnerID 'Value',
TBL.Partner 'Name',
CampaignCount.c1Count
FROM
dbo.tblPartner TBL (NOLOCK)
JOIN dbo.tblUser USR
ON TBL.PartnerID = USR.PartnerID join
(select t1.partnerid PartID, count(c1.partnerid) c1count
from dbo.tblPartner t1
join dbo.campaign c1
on t1.partnerid = c1.partnerid
group by t1.partnerid) CampaignCount
on tbl.partnerID = CampaignCount.PartID
WHERE
USR.AccessLevelID = 1 and
USR.PassportID IS NOT NULL
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 28, 2005 at 7:39 am
Plz don't cross post..
I replied to your question here...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=214703
But, I thought you just wanted to ensure they had at least one campaign. If not (ie, you want to show the number of campaigns), some sort of group by should do the trick for you.
Cheers,
Ian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply