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
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:
>
> You should change your app to enforce your rules. That is, your app should
> prevent your data entry folks from entering incomplete records. Otherwise,
it's
> 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
DIVISION!=''
> >
> > 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
met.
> 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
results.
>
> > 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
mark@stripped
602 524-0376
480 945-9197 fax
