• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • 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 RileyFounderCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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