INNER JOIN QUERY - Conversion Error

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

INNER JOIN QUERY - Conversion Error

Author

Topic

sigad2015
Starting Member

1 Post

Posted - 2015-02-06 : 16:18:11

Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication

Below is the query I put together with INNER JOIN:

SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail

FROM Agency
INNER JOIN (Clients
INNER JOIN (Campaigns
INNER JOIN DirectMail
ON DirectMail.DirectMailID = Campaigns.DirectMailID)
ON Clients.ClientID = Campaigns.CampaignID)
ON Agency.AgencyID = Clients.AgencyID

GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type
ORDER BY Agency.PrimaryAgencyName

When I run the query, I recent the below error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.

The value mentioned in the above error message is the ClientID.

Below are the tables for reference (bold for entity name and underline for PK) and datatypes

Agency
AgencyID - nvarchar(255)
PrimaryAgencyName - nvarchar(255)

Campaigns
CampaignID- int
ClientID- nvarchar(255)
DirectMailID- nvarchar(255)

Clients
ClientID - nvarchar(255)
AgencyID - nvarchar(255)

DirectMail
DirectMailID - nvarchar(255)
Campaign - nvarchar(255)
Type - nvarchar(255)
Month - float

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-06 : 17:15:24

The issue is here: Clients.ClientID = Campaigns.CampaignID. They are different data types, and ClientID has non integer values which can't be joined to an integer column. Is the join condition correct? Shouldn't it be Clients.ClientID = Campaigns.ClientID?

Please use this syntax instead of the weird join syntax you've used:
SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail
FROM Agency
INNER JOIN Clients ON Agency.AgencyID = Clients.AgencyID
INNER JOIN Campaigns ON Clients.ClientID = Campaigns.ClientID
INNER JOIN DirectMail ON DirectMail.DirectMailID = Campaigns.DirectMailID
GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type
ORDER BY Agency.PrimaryAgencyName

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources