#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Match SQL syntax error (simple)


    Hi,

    Can anyone help me with this?

    I'm trying to do a simple select of matches across two tables.

    Customer Enrolled
    Location_Number_1

    Dealers with Portfolio
    Location_Number

    as shown below:

    select * from [Customer Enrolled].[Location_Number_1] where [Customers Enrolled].[Location_Number_1] = [Dealers with Portfolio].[Location_Number]

    I keep getting a syntax error.

    What have I got wrong?

    Thanks,
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    select * from [Customer Enrolled].[Location_Number_1] where [Customers Enrolled].[Location_Number_1] = [Dealers with Portfolio].[Location_Number]

    I keep getting a syntax error.
    1. You cannot name your column in the from clause. So
    Code:
    from [Customer Enrolled].[Location_Number_1]
    is one mistake.

    2. You cannot use a table in where clause if that table is not refered to in from clause. To overcome this use inner join as follow:
    Code:
    SELECT * 
      FROM [Customer Enrolled] 
      INNER JOIN [Dealers with Portfolio]
        ON  [Customers Enrolled].[Location_Number_1] = [Dealers with Portfolio].[Location_Number]
    and please please never use *, it is just a bad practice, always name the columns you want to return.

    Regards
    Mahdi Eftekhari
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by SnapDaddy
    I keep getting a syntax error.

    What have I got wrong?
    your FROM clause should have 2 tables mentioned in it

    right now it has none, just a column (which is invalid)

    edit: no response for 2 hours, and then i get beaten out by 1 minute
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by Mahdi Eftekhari
    1. You cannot name your column in the from clause. So
    Code:
    from [Customer Enrolled].[Location_Number_1]
    is one mistake.

    2. You cannot use a table in where clause if that table is not refered to in from clause. To overcome this use inner join as follow:
    Code:
    SELECT * 
      FROM [Customer Enrolled] 
      INNER JOIN [Dealers with Portfolio]
        ON  [Customers Enrolled].[Location_Number_1] = [Dealers with Portfolio].[Location_Number]
    and please please never use *, it is just a bad practice, always name the columns you want to return.

    Regards
    Mahdi Eftekhari
    Thank you

IMN logo majestic logo threadwatch logo seochat tools logo