Create views with union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Create views with union

Author

Topic

Bellus
Starting Member

29 Posts

Posted - 2007-09-17 : 10:22:06

Hey

I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:

I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.

so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.

What shall a do?

Thanks for all help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-17 : 10:24:41

[code]Select Customer_Name as [Name] from table1
union all
Select contact_name from table2[/code]

If the names are overlapping in both tables, you need to use Union instead of Union all.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Bellus
Starting Member

29 Posts

Posted - 2007-09-17 : 10:30:40

Ok, thanks!

But where do a define the new name username?

is it something like this?:

CREATE VIEW v_users AS SELECT mcs.contact_name UNION mcc.customer_name

FROM contact mcs, customer mcc...?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-17 : 22:01:42

No, should be :

CREATE VIEW v_users AS
select contact_name from contact where ...
union
select customer_name from customer where ...

Bellus
Starting Member

29 Posts

Posted - 2007-09-18 : 02:58:32

Thanks, it worked perfect!

Just a las question - is there other ways to solve the same problem?or is UNION, the best solution...

thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 04:14:26

quote:
Originally posted by Bellus

Thanks, it worked perfect!

Just a las question - is there other ways to solve the same problem?or is UNION, the best solution...

thanks!

UNION will avoid duplicates and order the resultset

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 05:49:36

"is there other ways to solve the same problem?or is UNION, the best solution"

If you just want a list of Names then the VIEW is fine.

A UNION has to "sort" the list in order that it can remove duplicates. This takes some extra processing time.

(Thus if you KNOW that you have NO duplicates, or you WANT to include duplicates, then use UNION ALL which does not need to Sort/De-dupe, as it will be faster).

I reckon the problem comes, for a login, where you need Unique IDs and Passwords etc.

We have this type of problem in that we allow Sub-accounts for logins.

So basically we do:

1) Check if User/Password exists in Main Table.
2) If not, then check if User/Password exists in Sub-Account Table.

So we do two processing steps, rather than use a view so that:

a) the majority of users will be in the main table, thus their login will be "fastest".
b) Save the potential extra processing time of the Union of two tables
c) Solve the problem of having duplicates in both tables - i.e. the Main Table entry will be used instead of the sub-account table

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:32:16

A more versatile VIEW can be this

CREATE VIEW dbo.vwMyView1
AS

SELECT		Customer_Name AS [Name],
		COUNT(*)
FROM		(
			SELECT	Customer_Name
			FROM	Table1

			UNION ALL

			SELECT	Contact_Name
			FROM	Table2
		) AS d
GROUP BY	Customer_Name

Then you not only get all names stored, you also get the number of times they are stored and can work from there

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 08:21:09

and possibly even:


CREATE VIEW dbo.vwMyView1
AS

SELECT		Customer_Name AS [Name],
		[Source],
		COUNT(*) AS [Count]
FROM		(
			SELECT	Customer_Name,
				'Table1' AS [Source]
			FROM	Table1

			UNION ALL

			SELECT	Contact_Name,
				'Table2' AS [Source]
			FROM	Table2
		) AS d
GROUP BY	Customer_Name, [Source]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:25:43

[code]CREATE VIEW dbo.vwMyView1
AS

SELECT Customer_Name AS [Name],
SUM(CASE WHEN [Source] = 'Table1' THEN 1 ELSE 0 END) AS [Source1],
SUM(CASE WHEN [Source] = 'Table2' THEN 1 ELSE 0 END) AS [Source2]
COUNT(*) AS [Total]
FROM (
SELECT Customer_Name,
'Table1' AS [Source]
FROM Table1

UNION ALL

SELECT Contact_Name,
'Table2'
FROM Table2
) AS d
GROUP BY Customer_Name[/code]

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-09-18 : 10:06:15

Thanks a lot for all good advice!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources