1

I am trying to join 2 data extensions and input the result to a third data extension:

SELECT [Account ID] AS [Account ID], [Created Date Backend] AS [Created Date] 
FROM [DE A]  A
INNER JOIN [DE B] B
WHERE A.[Account ID] = B.[Lead ID]

Currently getting a syntax error:

The query syntax is invalid
Incorrect syntax near the keyword 'WHERE'.

What am I doing wrong?

4

When conducting a JOIN function, you need to specify what the 2 tables are joining on.

Try this:

SELECT 
[Account ID] AS 'Account ID'
,[Created Date Backend] AS 'Created Date'
FROM [DE A] A
INNER JOIN [DE B] B ON A.[Account ID] = B.[Lead ID]

Note that you may need to specify what table Account ID and Created Date Backend come from.

Reference: https://www.w3schools.com/sql/sql_join.asp

This image will help you understand the basics of JOINs: SQL Joins

  • DE B has a column called amounts. I am trying to aggregate the value in the amounts column for those A.[Account ID] = B.[Lead ID]. I am trying the following query SELECT A.[Account ID] AS [Account ID], A.[Created Date Backend] AS [Created Date], sum(B.[Amount]) AS [Amount] FROM [DE A] AS A INNER JOIN [DE B] AS B ON A.[Account ID] = B.[Lead ID] GROUP BY B.[Lead ID]. However got the following error : Column 'C7229974.A.Account ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – mit88 Mar 22 '17 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.