By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Different results in mysql & mysql query browser?

P: n/a
Hi everyone,

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
light.

Ok, the query (I've stripped it bare, the real query is a bit more
complex)...

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
be NULL.

so, for all intensive purposes, the schema is roughly:

people:
personId int unsigned not null auto_increment
place int

suburbs
locationId int unsigned not null auto_increment
suburbName varchar(50) not null

QUERY
------------------------------------------------------
SELECT DISTINCT (personId),place
FROM suburbs, people
WHERE
(
people.place = suburbs.locationId //mapping the place integer
to a suburb name
)
OR
(
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?

Cheers

Taras

Version info:

MySQL version: 5.0.19-nt via TCP/IP
MySQL Query Browser version: 1.1.20

Apr 18 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.