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.
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 != ''
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
