Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

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
0
Ray Paseur
Asked:
Ray Paseur
3 Solutions
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
"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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now