Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Unanswered: Rewrite this SUBQUERY

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Rewrite this SUBQUERY

    Try:
    Code:
    SELECT DISTINCT ccontrib.busname, ccontrib.bprov, ccontrib.bcity
    FROM ccontrib, category CA, category BA
    WHERE ccontrib.contcode = BA.contcode AND CA.catcode = '1itymd' AND BA.catcode = '1mbr1' AND CA.contcode = BA.contcode;

  3. #3
    Join Date
    Oct 2002
    Posts
    24

    Yup that's the ticket

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •