List:General Discussion« Previous MessageNext Message »
From:Mark Phillips Date:January 23 2006 8:23pm
Subject:Need help with a query
View as plain text  
I am running mysql 4.0.24 on Debian sarge.

I have a table with two columns, "team" and "division", both varchar(255). 
There are some errors in the table where division has a value but team is 
blank. Given that I am getting new data, and the data entry folks may create 
a record with a blank division and a team, I thought I would avoid any issues 
with team or division being blank as follows:

SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!=''

That returns what I expected - all division-team fields have data

Then I decided to order the output, so I added an ORDER BY clause and some 
parenthesis to make the sql more readable:

SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
DIVISION!='') ORDER BY division, team

But, I still get records with a blank team field (even if I remove the 
parenthesis). I finally found a solution, but I do not understand it:

SELECT DISTINCT division, team FROM registered WHERE (team!='' AND 
DIVISION!='') ORDER BY division, team

Why does the ORDER BY clause require an AND in the WHERE clause to work 
correctly?? I do not understand the logic.

Thanks for any insight you can share with me.

-- 
Mark Phillips
Phillips Marketing, Inc
mark@stripped
602 524-0376
480 945-9197 fax
Thread
Need help with a queryMark Phillips23 Jan
  • Dump only data and DatabaseLuiz Rafael Culik Guimaraes23 Jan
    • Re: Dump only data and DatabasePaul DuBois24 Jan
  • Re: Need help with a queryMichael Stassen23 Jan
    • Re: Need help with a queryMark Phillips24 Jan
  • Re: Dump only data and DatabaseLuiz Rafael Culik Guimaraes24 Jan
  • Re: Dump only data and DatabaseHarald Fuchs24 Jan
RE: Dump only data and DatabaseSST - Adelaide)24 Jan