I have a table of users, one of which has '' (empty string) as the username.
I issue the following select statement:
SELECT NameFormat(surname,initials) AS name,
users.username AS link,
email,
allowance,
CASE WHEN passwords.username IS NULL
THEN '' ELSE 'Y' END AS local,
surname,
initials
FROM users LEFT OUTER JOIN passwords
ON users.username=passwords.username
WHERE users.username<>'';
This works fine, listing 171 users (all except the one with the empty string
as the username).
Then I try making this a view:
CREATE VIEW user_list AS [the same select statement as above];
Now when I do "SELECT * FROM user_list" I get 172 results, and this includes
the one with the empty string as its name.
Anyone got any ideas why this should be so? The only way to get the expected
answer is to say SELECT * FROM user_list WHERE link<>'', which seems a bit
perverse to me...
----------------------------------------------------------------------
John English | mailto:[EMAIL PROTECTED]
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
