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
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
ASKER
HainKurt,
Using what you provided I get the exact same error (with "as df" in the error of course).
Using what you provided I get the exact same error (with "as df" in the error of course).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi!
I think the problem is in the last line of the sql
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
Regards,
Tomas Helgi
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
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
eg.
WHERE dsf.option_code IS NOT NULL
you will tell the MySQL optimizer to choose that particular column on that particular table. Regards,
Tomas Helgi
ASKER
I found the answer through extensive research.
Open in new window