Solved

MySQL VIEW used in SELECT Query

Posted on 2014-09-24
8
740 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 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 108

Author Comment

by:Ray Paseur
ID: 40342778
@Johnny:  Thanks for the heads-up. I'll email you separately about this.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 350 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 108

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 48

Expert Comment

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now