[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Changing an Access SQL statement in Mysql

Hello,

We are used to doing things in Access, but have moved to MySQL because we are growing and it is is more flexible.  

I am trying to write some MySQL queries to take the place of Access Queries we used to use.  This one gives me an error in MySQL.  Can someone point me in the right direction?
SELECT weborders.OrderID, weborders.CustomerID, customerwebid.[Customer Name], customerwebid.[Bank Name], [customer list].Address
FROM (weborders LEFT JOIN customerwebid ON weborders.CustomerID = customerwebid.[Customer ID]) LEFT JOIN [customer list] ON customerwebid.[Bank Name] = [customer list].[Bank Name]

Open in new window

0
hydrazi
Asked:
hydrazi
3 Solutions
 
Nathan RileyFounder/CTOCommented:
Try This
SELECT weborders.OrderID, 
weborders.CustomerID, 
customerwebid.[Customer Name], 
customerwebid.[Bank Name], 
[customer list].Address
FROM weborders LEFT JOIN customerwebid ON weborders.CustomerID = customerwebid.[Customer ID] LEFT JOIN [customer list] ON customerwebid.[Bank Name] = [customer list].[Bank Name]

Open in new window

0
 
rockiroadsCommented:
Whats the error?
Im assuming you are using linked tables and this is not a passthru query.
0
 
hydraziAuthor Commented:
Gallitin, your solution got me this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Customer Name], customerwebid.[Bank Name], [customer list].Address FROM webor' at line 3

I am running the query in PhpMyAdmin... I do not know what the difference is in linked tables or passthru.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mds-cosCommented:
I don't see anything that I can point to as specifically wrong, but a couple of general comments:

1)  Verify your data types on the joined fields, just to be sure something didn't get messed up when you upsized the data store from Access to MySQL.

2)  Try without the parenthesis that Access loves to put in, like this:

   SELECT weborders.OrderID, weborders.CustomerID, customerwebid.[Customer Name],
                 customerwebid.[Bank Name], [customer list].Address
   FROM weborders
        LEFT JOIN customerwebid ON weborders.CustomerID = customerwebid.[Customer ID]
        LEFT JOIN [customer list]  ON customerwebid.[Bank Name] = [customer list].[Bank Name]
 

2)  The spacing in table names is BAD practice -- I'm not entirely sure that MySQL will handle this.  If removing the parenthesis does not work try making a copy of "customer list" as "CustomerList" and run your query.

3)  Spacing in field names is also bad practice, but I am 95% certain that MySQL is fine with this.  However, for any future development you should never, never, never use spaces when naming database fields.
0
 
hydraziAuthor Commented:
I just downloaded NaviCat and muddled through it and came up with this, which works great!
SELECT
weborders.CustomerID,
weborders.Attention,
weborders.Date,
weborders.Buyer,
weborders.Seller,
customerwebid.`Customer Name`,
customerwebid.`Bank Name`,
`customer list`.`Bank Name`,
`customer list`.Address,
`customer list`.`Address 2`,
`customer list`.City,
`customer list`.State,
`customer list`.`Zip Code`
FROM
weborders
Left Outer Join customerwebid ON customerwebid.`Customer ID` = weborders.CustomerID
Left Outer Join `customer list` ON `customer list`.`Bank Name` = customerwebid.`Bank Name`

Open in new window

0
 
rockiroadsCommented:
From that query then I am assuming it is a passthru query as that is mysql syntax.
Navicat is a very good tool, Ive used it in the past and its one of the best Ive come across.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now