CODEWITHSUNDEEP
sqlsql-serverviewleft-join
Adhitya Sanusi
Adhitya Sanusi

Reputation: 119

SQL - Create View from Multiple Views

Just wondering, is it possible to create view from multiple views?

I'm trying to create a view (Data_extract_C) from 2 views (Data_extract_A and Data_Extract_B) by using Left Join. It's been an hour already and it still hasn't finished compiling the result. Is it normal?

Below is the syntax that I'm using. 

 Create view [TeamA].[Data_Extract_C] as

    select distinct

    [TeamA].[Data_Extract_A].Unique_ID, 
    sum ([TeamA].[Data_Extract_A].TotalFilesInput), 
    sum ([TeamA].[Data_Extract_B].TotalFilesOutput)

    from [TeamA].[Data_Extract_A]

    left join 

    [TeamA].[Data_Extract_B] 

    on 

    [TeamA].[Data_Extract_A].Unique_ID = [TeamA].[Data_Extract_B].Unique_ID 

    group by [TeamA].[Data_Extract_A].Unique_ID

go

Thanks all for your help. SQL Server 2012 is really new for me as I usually do data analysis using Excel.

Upvotes: 0

Views: 5247

Answers (3)

Sandeep Suthar
Sandeep Suthar

Reputation: 57

Hay Adhitya Sanusi try This..

First Using Join

 CREATE VIEW [TeamA].[Data_Extract_C] 
    as  SELECT 
     [TeamA].Data_Extract_A].Unique_ID, 
    sum ([TeamA].[Data_Extract_A].TotalFilesInput), 
    sum ([TeamA].[Data_Extract_B].TotalFilesOutput)   from 
  [TeamA].[Data_Extract_A] LEFT JOIN [Data_Extract_B]
   [TeamA].[Data_Extract_A].Unique_ID = [TeamA].[Data_Extract_B].Unique_ID 
      Group by [TeamA].[Data_Extract_A].Unique_ID

using of UINON

CREATE VIEW myview as


SELECT ... FROM View1......
   UNION
   SELECT ... FROM View2 ....

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I am guessing that you really want this:

select Unique_ID, 
       sum(TotalFilesInput), 
       sum(TotalFilesOutput)
from ((select a.Unique_ID, a.TotalFilesInput, 0 as TotalFilesOutput
       from [TeamA].[Data_Extract_A] a
      ) union all
      (select b.Unique_ID, 0 as TotalFilesInput, b.TotalFilesOutput
       from [TeamA].[Data_Extract_B] b
      )
     ) ab
group by Unique_ID;

Your poor performance is probably caused by a bad join -- multiple rows with the same value on both sides.

Upvotes: 1

Eto Kto
Eto Kto

Reputation: 96

Just wondering, is it possible to create view from multiple views?

Yes it's possible. You can reference views in other views. So your example of creating view out of joining two other views should work.

It's been an hour already and it still hasn't finished compiling the result. Is it normal?

I'm not sure about this part, i.e. answer is "it depends". Depends on what? Several factors like how large the tables are and how complex the other views ([TeamA].[Data_Extract_A], [TeamA].[Data_Extract_B]) are, what indexes you have (or don't have), etc.

Upvotes: 1

Related Questions