CODEWITHSUNDEEP
sql-server
user1330974
user1330974

Reputation: 2616

The column "xxxx" was specified multiple times for "CTE_YYYY"

I am new to Microsoft SQL Server. I am trying to join two tables that has common key named CampaignID using LEFT OUTER JOIN. I need to reuse the result in a different query, so I decided to capture the result set using CTE_Results. For example,

-- This is my CTE script
WITH CTE_Results AS
(
  SELECT t1.CampaignID, t2.CampaignID, t1.Name, t2.Vendor
  FROM CampaignDetails AS t1
  LEFT OUTER JOIN CampaignOnlineDetails AS t2
  ON t1.CampaignID = t2.CampaignID
)

-- This is the script I want to use to compare the resulting table. For example,
SELECT Vendor
FROM CTE_Results

However, when I ran above, I get:

The column `CampaignID` was specified multiple times for `CTE_Results`.

From reading through old StackOverflow questions and answers, it seems like since CampaignID is in both tables that are being joined, I must use table aliases to specify whose (which table's) CampaignID I want to SELECT. But I think I did that and even that it seems like the error still occurs.

Is there a way for me to select and keep BOTH CampaignID's in my CTE? If so, what should be changed? Thank you for the answers!

Upvotes: 2

Views: 7994

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

You have CampaignID selected twice in CTE, use different alias name to fix the problem 

WITH CTE_Results
     AS (SELECT t1.CampaignID AS cd_CampaignID,
                t2.CampaignID AS cod_CampaignID,
                t1.NAME,
                t2.Vendor
         FROM   CampaignDetails AS t1
                LEFT OUTER JOIN CampaignOnlineDetails AS t2
                             ON t1.CampaignID = t2.CampaignID)
-- This is the script I want to use to compare the resulting table. For example,
SELECT Vendor
FROM   CTE_Results

or use this

WITH CTE_Results(cd_CampaignID, cod_CampaignID, NAME, Vendor)
     AS (SELECT t1.CampaignID,
                t2.CampaignID,
                t1.NAME,
                t2.Vendor
         FROM   CampaignDetails AS t1
                LEFT OUTER JOIN CampaignOnlineDetails AS t2
                             ON t1.CampaignID = t2.CampaignID)
-- This is the script I want to use to compare the resulting table. For example,
SELECT Vendor
FROM   CTE_Results

Upvotes: 5

jkdba
jkdba

Reputation: 2519

You need to Alias the CampaignID Columns in your CTE or define the returned column names in the CTE declaration. Otherwise it would be like creating a table with two columns with the same name.

Example Column Alias:

WITH CTE_Results AS
(
  SELECT t1.CampaignID as 'CampaignID1', t2.CampaignID as 'CampaignID2', t1.Name, t2.Vendor
  FROM CampaignDetails AS t1
  LEFT OUTER JOIN CampaignOnlineDetails AS t2
  ON t1.CampaignID = t2.CampaignID
)

Or In CTE declaration:

WITH CTE_Results (CampaignID1, CampaignID2, [Name], Vendor) AS 
(
  SELECT t1.CampaignID, t2.CampaignID , t1.Name, t2.Vendor
  FROM CampaignDetails AS t1
  LEFT OUTER JOIN CampaignOnlineDetails AS t2
  ON t1.CampaignID = t2.CampaignID
)

Upvotes: 1

Related Questions