List:General Discussion« Previous MessageNext Message »
From:Mark Phillips Date:January 23 2006 11:03pm
Subject:Re: Need help with a query
View as plain text  
On Monday 23 January 2006 03:33 pm, Michael Stassen wrote:
> Mark Phillips wrote:
> > 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 
> > a record with a blank division and a team, I thought I would avoid any 
> > with team or division being blank as follows:
> You should change your app to enforce your rules.  That is, your app should 
> prevent your data entry folks from entering incomplete records.  Otherwise, 
> garbage in, garbage out.

You are absolutely correct. However, it is not my app nor do I control how the 
data is input into it. I just get a dump of the data to work with. :-(
> > SELECT DISTINCT division, team FROM registered WHERE team!='' OR 
> > 
> > That returns what I expected - all division-team fields have data
> I doubt it.  You've joined your two conditions with "OR", so your WHERE 
> condition will be true for any row with at least one of the two conditions 
>   Only a row with *both* fields blank would be excluded.  Remember,
>    NOT(A OR B) = NOT(A) AND NOT(B)
> so you should have used "AND".  You see?  A row you don't want has
>    team = '' OR DIVISION = ''
> so a row you do want has
>    NOT(team = '' OR DIVISION = '')
> which is equivalent to
>    team != '' AND DIVISION != ''
I feel so stupid. Not sure why I missed that - it is so basic! 

Well, that is my first Home Simpson of the week Doh!

Thanks !

> > Then I decided to order the output, so I added an ORDER BY clause and some 
> > parentheses to make the sql more readable:
> > 
> > SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
> > DIVISION!='') ORDER BY division, team
> The parentheses are irrelevant.  The ORDER BY cannot have changed which rows 
> were returned.  Perhaps the ordering facilitated noticing the unwanted 
> > 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
> As I explained above.
> > Why does the ORDER BY clause require an AND in the WHERE clause to work 
> > correctly?? I do not understand the logic.
> ORDER BY has nothing to do with it.
> > Thanks for any insight you can share with me.
> Michael

Mark Phillips
Phillips Marketing, Inc
602 524-0376
480 945-9197 fax
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