Solved

MySQL VIEW used in SELECT Query

Posted on 2014-09-24
8
756 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 109

Author Comment

by:Ray Paseur
ID: 40342778
@Johnny:  Thanks for the heads-up. I'll email you separately about this.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
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 109

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 109

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where on a calculated field 1 22
PHP 5.6 and 7.x 4 23
SQL Recursion schedule 13 16
How to extract database info from current month and year 7 13
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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

829 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