Merge two views into 1

• Mark Parter-146528

  Grasshopper

  Points: 13

  More actions

  June 3, 2004 at 2:45 am

  #108372

  I have the following 2 views in my SQL database. How can I "merge" these into 1 view?

  Views

  CREATE VIEW dbo.vwMCA

  AS

  SELECT     mod_code, SUM(smr_mcrd) AS sum_smr_mcrd

  FROM         dbo.ins_smr

  GROUP BY mod_code

  CREATE VIEW dbo.vwMCB

  AS

  SELECT     mod_code, SUM(smo_mcrd) AS sum_smo_mcrd

  FROM         dbo.cam_smo

  GROUP BY mod_code

  Tables and values

  CREATE TABLE [dbo].[ins_smr] (

   [mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,

   [smr_mcrd] [int] NULL

  ) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[cam_smo] (

   [mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,

   [smo_mcrd] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)

  INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)

   

  Thanks,

  Mark

• rockmoose

  SSCarpal Tunnel

  Points: 4601

  More actions

  June 3, 2004 at 3:04 am

  #508785

  Suggest: ( have omitted the CREATE VIEW statement )

  1.

  SELECT

   twotab.mod_code,

   SUM(twotab.sum_smo_mcrd) AS sum_smr_mcrd,

   SUM(twotab.sum_smr_mcrd) AS sum_smr_mcrd

  FROM

   ( SELECT mod_code, 0 as sum_smo_mcrd, SUM(smr_mcrd) AS sum_smr_mcrd

   FROM dbo.ins_smr

   GROUP BY mod_code

   UNION ALL

   SELECT mod_code, SUM(smo_mcrd) AS sum_smo_mcrd, 0 AS sum_smr_mcrd

   FROM dbo.cam_smo

   GROUP BY mod_code ) AS twotab

  GROUP BY

   twotab.mod_code

  2.

  SELECT

   twotab.mod_code,

   SUM(twotab.sum_smr_mcrd) AS sum_total

  FROM

   ( SELECT mod_code, SUM(smr_mcrd) AS sum_smr_mcrd

   FROM dbo.ins_smr GROUP BY mod_code

   UNION ALL

   SELECT mod_code, SUM(smo_mcrd)

   FROM dbo.cam_smo GROUP BY mod_code ) AS twotab

  GROUP BY

   twotab.mod_code

  /rockmoose

  
  You must unlearn what You have learnt

• Mark Parter

  Grasshopper

  Points: 17

  More actions

  June 3, 2004 at 5:10 pm

  #508912

  Thanks