code to use from access to mysql

[leewisener]

I am just trying out a conversion from access to mysql and hit only 1 snag, which is pleasing.

When I try to run some queries that contain the following code:

In (select[name]from[collector])

It returns an error:

ODBC failed: you have an error in your syntax.

the query is basically asking for a return of data but also to look in the collector table and use the name it finds there to return records = to that name.

Has anyone got any ideas as to how to get around this.

Thanks

 

[hos2]

In (select name from collector) should do it

I don't recognize the use for the [] signs ??

 

[leewisener]

sorry, they were not there when I copied that line from access to this post, wierd!

Anyway Access does like the code and accepts it, however maybe it would help if you see all the sql code from access:

SELECT payments.Date, payments.[Borrowers Name], Worklist.[Acc No], payments.[Payment Type], payments.Amount, Worklist.Sols, Worklist.Stage, Worklist.MIA, Worklist.collector
FROM payments INNER JOIN Worklist ON payments.[Account No] = Worklist.[Acc No]
WHERE (((payments.Date)=Date()) AND ((Worklist.collector) In (select[name]from[collector])))
WITH OWNERACCESS OPTION;

Although generating an odbc error at the second last line above I think its probably in the context how it fits in with the overall code rather than that line being wrong.

 

[hos2]

it depends on you're mysql version if subselects are allowed perhaps download the latest version

 

[r937]

no need to install 4.1 yet (it is not stable) ;-)

most subqueries can be done with joins anyway

[tt]select payments.`Date`
, payments.`Borrowers Name`
, Worklist.`Acc No`
, payments.`Payment Type`
, payments.Amount
, Worklist.Sols
, Worklist.Stage
, Worklist.MIA
, Worklist.collector
from payments
inner
join Worklist
on payments.`Account No`
= Worklist.`Acc No`
inner
join collector
on Worklist.collector
= collector.name
where payments.`Date` = Date()[/tt]

tip: in mysql, "bad" column names, i.e. with embedded spaces or same as a reserved word, need to be delimited with backticks -- this can be a pain, so it's better to rename the columns


rudy

http://r937.com/

SQL Consulting