CODEWITHSUNDEEP
sqlsql-server
bez91
bez91

Reputation: 136

Syntax Error SQL Join Statement

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!

Upvotes: 0

Views: 97

Answers (5)

DiegoAlfonso
DiegoAlfonso

Reputation: 257

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

Upvotes: 1

Pascalz
Pascalz

Reputation: 2378

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'

Upvotes: 0

Iti Tyagi
Iti Tyagi

Reputation: 3661

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';

Upvotes: 0

bgs
bgs

Reputation: 3213

         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';

Upvotes: 0

idipous
idipous

Reputation: 2910

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';

Upvotes: 0

Related Questions