Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL VIEW used in SELECT Query

Posted on 2014-09-24
8
Medium Priority
?
769 Views
Last Modified: 2014-09-25
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
0
Comment
Question by:Ray Paseur
8 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 600 total points
ID: 40342689
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
 

Expert Comment

by:Johnny
ID: 40342726
@Ray
my bad its  UNION ALL
here is fiddle to test (taken from original post)
http://sqlfiddle.com/#!9/72b47/1
0
 
LVL 111

Author Comment

by:Ray Paseur
ID: 40342778
@Johnny:  Thanks for the heads-up. I'll email you separately about this.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1400 total points
ID: 40343385
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1400 total points
ID: 40343450
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
 
LVL 111

Author Closing Comment

by:Ray Paseur
ID: 40343878
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40345115
"best approach will be to do some reorganization of these tables!"
absolutely!
0
 
LVL 111

Author Comment

by:Ray Paseur
ID: 40345321
@PortletPaul: Where is the +1 key when I need it!?

Thanks for all you give to E-E, ~Ray
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question