Hi Folks,

I've been working on this database for a while now and JUST realized that the MySQL version is 4.0.x and not 4.1, so this query that runs great in Access doesn't work on MySQL. Until we upgrade to 4.1 when it's stable, I need to rewrite this query in old style.

Can anyone help?

SELECT DISTINCT ccontrib.busname, ccontrib.bprov, ccontrib.bcity
FROM ccontrib, (SELECT DISTINCT CA.catcode, CA.contcode, BA.catcode, BA.contcode
FROM category CA, category BA
WHERE CA.catcode = '1itymd' AND BA.catcode = '1mbr1' AND CA.contcode = BA.contcode) AS B
WHERE ccontrib.contcode = B.BA.contcode;

Basically, ccontrib is the main details table, and category is one with no primary key that holds all of the various categories like '1itymd' and '1mbr1' under catcode. The contcode is consistent between the tables.

Thanks!
DW

Try:

Thanks aus, I got so caught up in looking up alternate solutions to the subquery thing that I didn't pause to think it out logically enough.

Thanks all the same, I got her back up and runnin

-DW