I have a SELECT query that names a VIEW as the object of the FROM clause. The VIEW, in turn, makes SELECT queries against three tables, normalizing the names of some of the columns with AS since they are inconsistently named in the tables. I'd like to know what to expect in these circumstances...
1. Is the VIEW fully dynamic, ie: if the data in one of the underlying tables changes, will the next SELECT FROM VIEW reflect the changed data in real time?
2. If there are duplicate data elements in these tables (username and password), and these duplicate data elements satisfy all of the WHERE conditions, is it predictable which table will return the rows at the top of the results set? If so, which table might it be in this VIEW and query?
A related question is available here:
This is the VIEW.
CREATE VIEW consolidate_users AS select ContactID,username,password,active,1 as user_type,ContactName as ExtraInfo from Contact
select NULL as ContactID,username,password,active,2 as user_type,CompanyName as ExtraInfo from Client
select NULL as ContactID,a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;
This is the query.
"SELECT UserID,username,user_type,ExtraInfo FROM consolidate_users WHERE active=1 and username='".$login_name."' and password='".$login_pw."'"
Thanks and regards, ~Ray