473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Different results in mysql & mysql query browser?

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
0 2193

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Chris Cox | last post by:
Hi, Hoping someone can help, I have a sql query which is passed to the script via the url, this is like:- &stmt=select%20*%20from%20table%20where%20(%20status!='Closed'%20..... If I look at...
0
by: Jim Moseby | last post by:
I stumbled across this while trying to update a table with a timestamp type column. At the time, I didn't know that the timstamp column would update itself when a row was changed. A kind gentleman...
2
by: Gnurp Gnarp | last post by:
When the number of columns get large, it becomes increasingly difficult to view the results of a select in a small window (e.g. 80 line xterm) because of line wrapping. Can I have the results of...
4
by: MLH | last post by:
A programmer developed an AMP (Apache/MySQL/PHP) application for me. When he was done, he sent me the PHP files and the MySQL dump file. Now, when I connect to the application on my LAN using...
0
by: Murphy | last post by:
I am currently migrating a db from SQL Server & MySQL and ? (front end yet to be decided upon) As I familiarise myself with MySQL populating tables etc can be quite clumbersome. After reading the...
5
by: princevejita1 | last post by:
Hello I have problem with my MySQL server and transactions. I installed MySQL server 5.0.11 with MySQL administrator & MySql Query Browser on Win XP Prof. I would like to use transactions, so I...
1
by: SC | last post by:
I'm developing a site that may eventually have a very large database of users (hopefully, but who knows). It will be a community website where users can search each other (think Friendster,...
5
by: smatta | last post by:
I have just installed MySql version 5.0-18 on Red Hat Fedora Core 4. It is running but I cant connect to it using MySql Query Browser running on my pc. >From my pc, I can telnet to the sql...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.