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 [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
