Strange query behavior

[Linus Nikander]

Whilst trying to figure out why one of my queries wasn't working I ran into
the following phenomenon which I'd be grateful if someone could explain.

(I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4
mysql control center)

The following query returns the expected result
SELECT
distinct(PO.error_type) AS error_type,
COUNT(PO.error_type) AS occurrence
FROM
problematic_odds AS PO
WHERE NOT
(PO.error_type = 100)
GROUP BY
PO.error_type

it returns 1008 ,212 for error_type and occurrence respectively.
When I change the query to

SELECT
distinct(PO.error_type) AS error_type,
COUNT(PO.error_type) AS occurrence
FROM
problematic_odds AS PO,
active_odds AS AO
WHERE NOT
(PO.error_type = 100)
GROUP BY PO.error_type

the query executes BUT doesn't return anything at all. I can't quite figure
out why. The only difference between the two queries is that the second one
has active_odds AS AO  added to the FROM clause. Even though I don't use
AO at all in the query the results still differ. Anyone have an explanation
? Having a superflous table in the WHERE clause shouldn't affect the query
outcome should it ?

//Linus Nikander - [EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Autocolumn and pk

[Linus Nikander]

I'm pretty new to mySQL so there might be an easy way of solving this
(hopefully)

Is there a way to create something akin to an Oracle Sequence in mySQL ?

I know of the autonumber feature. The problem is that the table that will be
using the key won't actually be using it as a unique pk. The pk will instead
consist of the autonumber + the value of another column. Thus, using
autonumber on a column of the target table isn't really an option.

I can think of one way of solving it. I could create a table with a single
autonumber column for the sole purpose of generating the numbers (when i
need them). That is going to clutter up my tables in the long run though,
and isn't really something I want to do. Is that the only viable solution,
or is there some functionality that I don't know of which does it better ?

//Linus Nikander - [EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]