ODBC Connection MySQL input data error

[bminderman]

I am getting an error when running an input from ODBC connection.  Alteryx cannot open a query that runs successfully in MySQL Workbench. I have been successful using the same ODBC connection to query the same MySQL server.  Does the syntax requirement vary in Alteryx SQL editor from what MySQL workbench requires? Are there restrictions or limitations to query?  

[r4upadhye]

Can you share your error? or screenshot?

Did you establish and test your connection ? Is ur connection running successfully ?

[bminderman]

Yes, connection works for generic queries .  The error message lists the sql code, followed by "no columns returned."

The SQL i'm attempting to run does include nested queries.  

[bminderman]

I see my screenshot did not post correctly, so I"m trying again

[r4upadhye]

Just saw ur error,

could u also post the entire sql query for ref.

[bminderman · Accepted solution]

r4upadhye,

The following is the code entered into SQL Editor in input tool that results in the error. Thanks for your help!

SELECT DISTINCT
cus.customer,
cus.registrationId,
cus.joined,
ic.industry_code AS industry_code_1,
class.title AS class
FROM precompile_accounts AS cus

LEFT JOIN (
SELECT DISTINCT rel.fid AS customer, rel.company AS company ,
IFNULL((SELECT 1 FROM tblCompanyRelationships AS mktg WHERE mktg.fid=rel.fid AND mktg.fid_type=2 AND mktg.company=rel.company AND mktg.relationship=2),0) AS relationship
FROM tblCompanyRelationships AS rel
WHERE rel.fid_type=2 AND (rel.relationship=5) AND rel.primary_id =1
GROUP BY rel.fid, rel.company, rel.relationship)
AS rel ON rel.customer = cus.customer

LEFT JOIN tblCompanies as com on rel.company = com.id
LEFT JOIN company_industry_code AS comind ON comind.company_id=rel.company AND comind.priority = 1
LEFT JOIN industry_code AS ic ON ic.industry_code = comind.industry_code
LEFT JOIN tblCompanyClasses AS class ON class.id=com.class

[bminderman · Accepted solution]

I was able to resolve by adding the database name to the table name before each from and join clause.  Thanks to r4upadhye for your assistance with this issue. Still not sure why I did not have to specify database name on previous queries using same ODBC connection.

SELECT DISTINCT
cus.customer,
cus.registrationId,
cus.joined,
ic.industry_code AS industry_code_1,
class.title AS class
FROM DatabaseName.precompile_accounts AS cus

LEFT JOIN (
SELECT DISTINCT rel.fid AS customer, rel.company AS company ,
IFNULL((SELECT 1 FROM DatabaseName.tblCompanyRelationships AS mktg WHERE mktg.fid=rel.fid AND mktg.fid_type=2 AND mktg.company=rel.company AND mktg.relationship=2),0) AS relationship
FROM DatabaseName.tblCompanyRelationships AS rel
WHERE rel.fid_type=2 AND (rel.relationship=5) AND rel.primary_id =1
GROUP BY rel.fid, rel.company, rel.relationship)
AS rel ON rel.customer = cus.customer

LEFT JOIN DatabaseName.tblCompanies as com on rel.company = com.id
LEFT JOIN DatabaseName.company_industry_code AS comind ON comind.company_id=rel.company AND comind.priority = 1
LEFT JOIN DatabaseName.industry_code AS ic ON ic.industry_code = comind.industry_code
LEFT JOIN DatabaseName.tblCompanyClasses AS class ON class.id=com.class