MySQL VIEW used in SELECT Query

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:
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28512556.html

This is the VIEW.
CREATE VIEW consolidate_users AS select ContactID,username,password,active,1 as user_type,ContactName as ExtraInfo from Contact 
union 
select NULL as ContactID,username,password,active,2 as user_type,CompanyName as ExtraInfo from Client 
union 
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;

Open in new window

This is the query.
"SELECT UserID,username,user_type,ExtraInfo FROM consolidate_users WHERE active=1 and username='".$login_name."' and password='".$login_pw."'"

Open in new window

Thanks and regards, ~Ray
LVL 111
Ray PaseurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi Ray

>1. Is the VIEW fully dynamic
Yes, as long as it's not an indexed/materialized view.

>2. If there are duplicate data elements in these tables
a. If all columns are duplicates, then the UNION will return only one, as UNION eliminates duplicates.  To show duplicates use UNION ALL.
b.  I think they appear in the order of the SQL.  If you wish to override this, or you just want to be sure, throw it in a subquery like this T-SQL example...
SELECT a.id, a.name
FROM (
   SELECT id, name, 1 as sort_order FROM apples
   UNION
   SELECT id, name, 2 as sort_order FROM grapes
   UNION
   SELECT id, name, 3 as sort_order FROM bananas) a
ORDER BY a.sort_order, a.name

Open in new window

0
JohnnyCommented:
@Ray
my bad its  UNION ALL
here is fiddle to test (taken from original post)
http://sqlfiddle.com/#!9/72b47/1
0
Ray PaseurAuthor Commented:
@Johnny:  Thanks for the heads-up. I'll email you separately about this.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

PortletPaulfreelancerCommented:
On 1. above. Yes. it is fully dynamic.

Regarding the query itself:
Consider the constant that is embedded into to each subquery of the overall union

,1 as user_type
,2 as user_type
,3 as user_type


Then, let there be just one source record "select 'X' as usercode"

Now combine these:

select  'X' as usercode,1 as user_type
UNION
select  'X' as usercode,2 as user_type
UNION
select  'X' as usercode,3 as user_type

The result - even with UNION is 3 rows.

USERCODE USER_TYPE
X                   1
X                   2
X                   3

because although X is common, the constants used in the query force the rows to be different.

If you refer back to the original question I was simply pointing out that UNION ALL should be used for performance, because although 'ALL' sounds like it does more in fact the reverse is true. UNION performs a "distinct sort" which is quite pointless for that query.

NB:
That view will NOT produce a unique set of: username & password
either with UNION or with UNION ALL

--------
by the way, the answer to sub-question 2 is:
No. If rows are excluded because of a UNION it is not predictable which source table is used in the result,

It also does not matter because to have survived the "distinct sort" an excluded row is exactly the same as one retained in the result and hence "the surviving row" exists in at least one, perhaps more, tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Sorry, thinking about the intent of sub-question 2.

" 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?"

For THAT query, the USER_TYPE column actually indicates which source table the record comes from (in fact that is the purpose of that column)

USER_TYPE
1 = Contact
2 = Client
3 = EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;
0
Ray PaseurAuthor Commented:
Thanks to all.  From this and other related information, I've concluded that the best approach will be to do some reorganization of these tables!
0
PortletPaulfreelancerCommented:
"best approach will be to do some reorganization of these tables!"
absolutely!
0
Ray PaseurAuthor Commented:
@PortletPaul: Where is the +1 key when I need it!?

Thanks for all you give to E-E, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.