Do you have a question? Post it now! No Registration Necessary. Now with pictures!
April 18, 2006, 1:05 pm
rate this thread
I've come across an extremely strange problem. The exact same query in
both mysql command line client, and mysql query browser gives entirely
different results. I was hoping someone out there could shed some
Ok, the query (I've stripped it bare, the real query is a bit more
Each person in the people table has an associated 'place', which is an
integer that maps onto a suburb in the suburbs table. The 'place' CAN
so, for all intensive purposes, the schema is roughly:
personId int unsigned not null auto_increment
locationId int unsigned not null auto_increment
suburbName varchar(50) not null
SELECT DISTINCT (personId),place
FROM suburbs, people
people.place = suburbs.locationId //mapping the place integer
to a suburb name
people.place IS NULL
AND suburbs.locationId = 1
Why do I set suburbs.locationId = 1? Because we are doing a cartesian
join, we don't want an entire suburbs table for each entry in the
people table where the place is null - ie: it ensures that each line in
the person table is associated with exactly one line in the suburbs
table. Since we don't care about the suburb name when the place is
null, we put in a mock value.
In MySQL query browser, I get 21 results. When I copy and paste the
query into a command line instance of MySQL, I get the (required)
result of 95.
If I delete the line 'AND suburbs.locationId = 1', I get the required
result of 95 in both query browser and mysql command line.
Anyone have any ideas?
MySQL version: 5.0.19-nt via TCP/IP
MySQL Query Browser version: 1.1.20
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum