Merge a 3 tables data in one view

  • I have 3 tables as below,

    USE [Dummy_New]

    GO

    /****** Object: Table [dbo].[College_CutoffMaster] Script Date: 01/11/2012 13:15:02 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[College_CutoffMaster](

    [CutoffId] [numeric](18, 0) NOT NULL,

    [Cutoff] [numeric](18, 0) NULL,

    [CollegeId] [numeric](18, 0) NOT NULL,

    [DegreeId] [numeric](18, 0) NOT NULL,

    [StreamId] [numeric](18, 0) NOT NULL,

    [EntranceId] [numeric](18, 0) NOT NULL,

    [Gender] [nchar](10) NOT NULL,

    [UniversityType] [nchar](2) NULL,

    [Capround] [nchar](10) NULL,

    CONSTRAINT [PK_College_CutoffMaster] PRIMARY KEY CLUSTERED

    (

    [CutoffId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[College_CutoffMaster] WITH NOCHECK ADD CONSTRAINT [FK_College_CutoffMaster_College_CutoffMaster] FOREIGN KEY([CutoffId])

    REFERENCES [dbo].[College_CutoffMaster] ([CutoffId])

    GO

    ALTER TABLE [dbo].[College_CutoffMaster] CHECK CONSTRAINT [FK_College_CutoffMaster_College_CutoffMaster]

    GO

    USE [Dummy_New]

    GO

    /****** Object: Table [dbo].[College_HMCTCutoffMaster] Script Date: 01/11/2012 13:16:35 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[College_HMCTCutoffMaster](

    [HmctCutoffId] [int] NOT NULL,

    [HmctCutoff] [numeric](18, 2) NOT NULL,

    [CollegeCode] [varchar](50) NOT NULL,

    [CollegeId] [numeric](18, 0) NOT NULL,

    [DegreeId] [numeric](18, 0) NOT NULL,

    [StreamId] [numeric](18, 0) NOT NULL,

    [EntranceId] [numeric](18, 0) NOT NULL,

    [CutoffStateName] [varchar](50) NOT NULL,

    [Gender] [nchar](10) NOT NULL,

    [UniversityType] [varchar](50) NOT NULL,

    [Capround] [nchar](10) NOT NULL,

    [CreatedOn] [datetime] NULL,

    [CreatedBy] [numeric](18, 0) NULL,

    [EditedOn] [datetime] NULL,

    [EditedBy] [numeric](18, 0) NULL,

    [Version] [int] NOT NULL,

    CONSTRAINT [PK_College_HMCTCutoffTransition] PRIMARY KEY CLUSTERED

    (

    [HmctCutoffId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    USE [Dummy_New]

    GO

    /****** Object: Table [dbo].[College_Maharashtra_ArchitectureCutoffmaster] Script Date: 01/11/2012 13:17:00 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[College_Maharashtra_ArchitectureCutoffmaster](

    [CutoffId] [numeric](18, 0) NOT NULL,

    [Cutoff] [numeric](18, 2) NULL,

    [CollegeId] [numeric](18, 0) NOT NULL,

    [DegreeId] [numeric](18, 0) NOT NULL,

    [StreamId] [numeric](18, 0) NOT NULL,

    [EntranceId] [numeric](18, 0) NOT NULL,

    [Gender] [nchar](10) NULL,

    [UniversityType] [nchar](10) NULL,

    [Capround] [nchar](10) NULL,

    [CollegeCode] [numeric](18, 0) NULL,

    [Rank] [numeric](18, 0) NULL,

    [CreatedOn] [datetime] NULL,

    [CreatedBy] [numeric](18, 0) NULL,

    [EditedOn] [datetime] NULL,

    [EditedBy] [numeric](18, 0) NULL,

    [Version] [int] NULL,

    CONSTRAINT [PK_College_Maharashtra_ArchitectureCutoffmaster] PRIMARY KEY CLUSTERED

    (

    [CutoffId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    I have data in it....

    here only i can make a view of individual table but can i make a view of above 3 tables as a one view..???? these three tables has totally different data...

    My colleague said it will be possible after using decode function.. but i think no.....

    please reply...

    Thanks & Regards,
    Pallavi

  • You haven't provided enough information for anyone to provide an accurate answer.

    What do you want the view to provide? A union of the results from the tables or a Join of the results of the tables?

    What are the relationships between the tables that would allow you to join them together? The only foreign key you have defined is:

    ALTER TABLE [dbo].[College_CutoffMaster] WITH NOCHECK ADD CONSTRAINT [FK_College_CutoffMaster_College_CutoffMaster] FOREIGN KEY([CutoffId])

    REFERENCES [dbo].[College_CutoffMaster] ([CutoffId])

    and I don't even know how that would work as you have column referencing itself so I don't even know how that would work since once you have cutOffID 1 in the database it can only reference itself since you can't insert another CutOffID 1 because it is also the Primary Key.

    Since I'm forced to guess I assume that you would JOIN college_cutoffmaster to college_Maharashtra_ArchitectureCutoffmaster on CutoffID, but I don't know that. I'm not sure how you'd join to the third table.

  • pallavi.unde (1/11/2012)


    ...can i make a view of above 3 tables as a one view..???? these three tables has totally different data...

    Assuming you figure out a way to join those tables, not problem at all.

    Ask yourself... how do I related rows on one table to rows on the other two tables?

    If you have a positive answer to that question just code it on your view's predicate and be happy forever after.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply