Link to home
Start Free TrialLog in
Avatar of pkromer
pkromer

asked on

MySQL query translation to Access

This query in Access results in the error in screenshot... is it the = sign?

SELECT dsf.qm_sku AS sku, dfv.option_code AS OptionColor, df.fsp_title AS OptionTitle
FROM dynamic_sku_fsp dsf
INNER JOIN dynamic_fsp_values dfv ON dfv.fsp_value_id = dsf.fsp_value_id
INNER JOIN dynamic_fsp df ON df.fsp_id = dfv.fsp_id
WHERE option_code IS NOT NULL

User generated image
Avatar of HainKurt
HainKurt
Flag of Canada image

try adding "AS" before aliases

SELECT dsf.qm_sku AS sku, dfv.option_code AS OptionColor, df.fsp_title AS OptionTitle
  FROM dynamic_sku_fsp as dsf
 INNER JOIN dynamic_fsp_values as dfv ON dfv.fsp_value_id = dsf.fsp_value_id 
 INNER JOIN dynamic_fsp as df ON df.fsp_id = dfv.fsp_id 
 WHERE option_code IS NOT NULL

Open in new window

Avatar of pkromer
pkromer

ASKER

HainKurt,

Using what you provided I get the exact same error (with "as df" in the error of course).
ASKER CERTIFIED SOLUTION
Avatar of pkromer
pkromer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tomas Helgi Johannsson
Hi!

I think the problem is in the last line of the sql
SELECT dsf.qm_sku AS sku, dfv.option_code AS OptionColor, df.fsp_title AS OptionTitle
FROM dynamic_sku_fsp dsf
INNER JOIN dynamic_fsp_values dfv ON dfv.fsp_value_id = dsf.fsp_value_id
INNER JOIN dynamic_fsp df ON df.fsp_id = dfv.fsp_id
WHERE option_code IS NOT NULL

Open in new window


as you have three sets of the tables and as your last comment shows there are two tables with the column option_code and the reason for your error in the first place is that the optmizer has no clue on what column and table to choose the data from.
So by adding dvf. or dsf. in front of the column name in
WHERE option_code IS NOT NULL 

Open in new window

eg.
WHERE dsf.option_code IS NOT NULL 

Open in new window

you will tell the MySQL optimizer to choose that particular column on that particular table.

Regards,
     Tomas Helgi
Avatar of pkromer

ASKER

I found the answer through extensive research.