Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge a 3 tables data in one view Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 12:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, January 11, 2012 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 11,157, Visits: 12,899
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
Posted Thursday, January 12, 2012 7:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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 »

Add to briefcase

Permissions Expand / Collapse