Syntax Error SQL Join Statement

Question

I have tables I wish to join. They are related by a customer match number and cust match no. I want to enter data from one table, then join with another then display the results from the second table. My code is shown below:

SELECT [title], 
       [customer_no], 
       [forename], 
       [surname], 
       [res_addr_1], 
       [res_addr_2], 
       [city],   
       [country], 
       [res_postcode], 
       [DOB], 
       [Home_phone_no], 
       [Mobile_phone_no] 
 FROM Customer
 LEFT JOIN Account ON cust_match_no 
      FROM Customer = customer_match_no 
      FROM Account  
 WHERE Account.account_no = '12345678';

However I keep getting errors with incorrect syntax near "=" and I have no idea why!

DiegoAlfonso · Accepted Answer · 2013-10-17 14:29:02Z

That's not what FROM clause is intended for. FROM specifies the "main" table in your query. In order to specify which fields from which tables should match within the JOIN, if there is any ambiguity, you need to qualify those fields with the name of the table or an alias, as you did with the WHERE clause: WHERE Account.account_no = .... In your case, it seems to be unnecessary, since the field from Account is named cust_match_no and the other one is customer_match_no, but in any case you can rephrase your query like this:

SELECT 
    [title], 
    [customer_no], 
    [forename], 
    [surname], 
    [res_addr_1], 
    [res_addr_2], 
    [city], 
    [country], 
    [res_postcode], 
    [DOB], 
    [Home_phone_no], 
    [Mobile_phone_no] 
FROM Customer cus
LEFT JOIN Account acc ON cus.cust_match_no = acc.customer_match_no
WHERE acc.account_no = '12345678';

The same advice applies in case of an ambiguous column name in your SELECT clause. Let's say there is a "country" field in both Customer and Account tables. Then, you should qualify those columns to disambiguate:

SELECT
    [Customer].[Country],
    [Account].[Country]
    ....

PS. Please excuse my english

idipous · Accepted Answer · 2013-10-17 14:01:56Z

0

SELECT [title], [customer_no], [forename], [surname],
[res_addr_1], [res_addr_2], [city], [country], [res_postcode], 
[DOB], [Home_phone_no], [Mobile_phone_no] FROM Customer
LEFT JOIN Account ON 
cust_match_no = customer_no WHERE Account.account_no = '12345678';

answered Oct 17, 2013 at 14:01

idipous

2,9103 gold badges38 silver badges45 bronze badges

Comments

bgs · Accepted Answer · 2013-10-17 14:10:01Z

0

         SELECT [title], [customer_no], [forename], [surname], [res_addr_1], [res_addr_2], 
[city],   [country], [res_postcode], [DOB], [Home_phone_no], [Mobile_phone_no] FROM Customer
LEFT JOIN Account ON Account.cust_match_no = Customer.customer_match_no 
WHERE Account.account_no = '12345678';

answered Oct 17, 2013 at 14:10

bgs

3,2537 gold badges43 silver badges59 bronze badges

Comments

Iti Tyagi · Accepted Answer · 2013-10-17 14:14:02Z

0

Try like this:

SELECT [title], 
   [customer_no], 
   [forename], 
   [surname], 
   [res_addr_1], 
   [res_addr_2], 
   [city],   
   [country], 
   [res_postcode], 
   [DOB], 
   [Home_phone_no], 
   [Mobile_phone_no] 
FROM Customer
LEFT JOIN Account ON cust_match_no = customer_match_no    
WHERE Account.account_no = '12345678';

answered Oct 17, 2013 at 14:14

Iti Tyagi

3,7018 gold badges53 silver badges82 bronze badges

Comments

Pascalz · Accepted Answer · 2013-10-17 14:17:54Z

0

try like this :

SELECT 
    [title],
    [customer_no], 
    [forename], 
    [surname], 
    [res_addr_1], 
    [res_addr_2], 
    [city], 
    [country], 
    [res_postcode], 
    [DOB], 
    [Home_phone_no], 
    [Mobile_phone_no] 
FROM 
    Customer 
LEFT JOIN 
    Account 
ON 
    cust_match_no = customer_match_no 
WHERE 
    Account.account_no = '12345678'

edited Oct 17, 2013 at 14:17

answered Oct 17, 2013 at 14:02

Pascalz

2,3581 gold badge24 silver badges25 bronze badges

Collectives™ on Stack Overflow

Syntax Error SQL Join Statement

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Collectives™ on Stack Overflow

Syntax Error SQL Join Statement

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest