January 11, 2012 at 12:51 am
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
January 11, 2012 at 8:55 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 12, 2012 at 7:22 am
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