A project that I've been working on is just about complete, but I've had to develop it locally on MySQL 4.1 for it's support of nested queries - I'm bad with joined queries.

However, the project is going to be running on my host, who runs MySQL 3.x, which doesn't support nested queries. Therefore I'm looking for help on converting the query below, to not use any subqueries so it'll work on my host.

Any Help would be great!

$sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN (";	
$sql .= "  SELECT DISTINCT u.referral FROM user u, transaction t WHERE u.id=t.userID";
$sql .= ") GROUP BY id";

    Not quite sure about what you're trying to do, but here's a stab:

    SELECT DISTINCT u.referral, u.*, COUNT(u.id) AS referrals FROM u.user, t.transaction WHERE u.id = t.userID

    I'm not great with distinct, so that's really just a guess.

      This query doesn't make a huge amount of sense, as you're:

      • COUNT(id) appears to be counting a column which is in GROUP BY, this is not logically consistent. Use aggregate functions on non-grouped columns.
      • Shouldn't there be exactly one row with each id anyway, assuming that id has a unique index on it?
      • You're selecting "*", but grouping by id, this probably won't work as it (presumably) includes non-grouped columns. You can't use ungrouped columns in a SELECT list except with an aggregate function.

      I can't see how the original query could possibly have achieved anything useful, or even worked.

      I believe that whatever it is you're trying to do, can probably be done with a plain join and a single GROUP BY.

      Mark

        jimmyp3016 wrote:

        A
        However, the project is going to be running on my host, who runs MySQL 3.x...

        I also recommend that you stop using that host. MySQL 3.x is ABSOLUTELY PREHISTORIC.

        It is only of archaeological interest, in my opinion. It lacks SO MANY useful features, there is no point developing against it (unless you're into ancient history).

        Mark

          MarkR wrote:

          I also recommend that you stop using that host. MySQL 3.x is ABSOLUTELY PREHISTORIC.

          It is only of archaeological interest, in my opinion. It lacks SO MANY useful features, there is no point developing against it (unless you're into ancient history).

          Mark

          I concur. When MySQL 3.x ruled the land, so did PHP 3.0, postgresql 7.1, java SDK 1.2, apache 1.3 or so, Red Hat Linux 5.2, etc...

          Can you imagine a hosting company having any of those other versions on their server? Other than the odd occasional occurance of apache 1.3 because they're using some module that doesn't yet work under 2.x, you don't really see any of those, and there's a dang good reason for it.

          But back to the query at hand.

          This form:

          select * from sometable group by onefield

          is absolutely wrong, unless onefield is a primary key, and even then, most databases won't let you run it. It's a mysqlism that generally results in a random assortment of data, which could change each time you run it.

          for instance:

          create table test (id int, val text);
          insert into test values (1,'abc');
          insert into test values (1,'def');
          select * from test group by id

          could return either 1,abc OR 1,def and you have no clue which one you'll get. If you're building a query like select * from table group by onefield all the time, you need to stop, get a cup of coffee, and rethink what you're trying to do.

            Write a Reply...