Merge a 3 tables data in one view

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Merge a 3 tables data in one view

Merge a 3 tables data in one view

Rate Topic

Display Mode

Topic Options

Author

Message

pallavi.unde

Posted Wednesday, January 11, 2012 12:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:35 AM
Points: 191, Visits: 170

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

Post #1233760

Jack Corbett

Posted Wednesday, January 11, 2012 8:55 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 10,613, Visits: 11,955

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1234063

PaulB-TheOneAndOnly

Posted Thursday, January 12, 2012 7:22 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 3,016, Visits: 4,470

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.

Post #1234800

« Prev Topic | Next Topic »

Permissions