dotNETportal.cz

Ahoj all,

potřeboval bych takovou v celku velkou pomoc a to takovou že ze čty níže uvedených tabulek vytvořit jeden VIEW (pohled) ale s tím že z tabulek bude v pohledu jen viz. níže

Users UserId, UserName, Email, Comment, Enabled a DateCreatedProfiles vše kromě duplicitníchStates vše kromě duplicitníchQuestions vše kromě duplicitních

dbo.Users

CREATE TABLE dbo.Users(
	UserId		int IDENTITY(1,1)	NOT NULL,
	UserName	varchar(100)		NOT NULL,
	PasswordHash	char(86)		NOT NULL,
	PasswordSalt	char(5)			NOT NULL,
	Email		varchar(100)		NOT NULL,
	Comment		text			NULL,
	Enabled		bit			NOT NULL,
	DateCreated	datetime		NOT NULL,
	DateLastLogin	datetime		NULL,
	DateLastActivity	datetime	NULL,
	DateLastPasswordChange	datetime	NOT NULL,
	
	CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserId ASC),
	CONSTRAINT IX_Users_UserName UNIQUE NONCLUSTERED (UserName ASC)
)

dbo.Profiles

CREATE TABLE dbo.[Profiles] (
	[ProfileId]	int IDENTITY(1,1)	NOT NULL,
	[UserName]	nvarchar(100)		NOT NULL,
	[LastUpdate]	datetime		NOT NULL,
	[FirstName]	nvarchar(max)		NOT NULL,
	[LastName]	nvarchar(max)		NOT NULL,
	[CompanyName]	nvarchar(max)		NULL,
	[Avatar]	image			NULL,
	[Street]	nvarchar(max)		NOT NULL,
	[HouseNumber]	nvarchar(max)		NOT NULL,
	[Adress]	nvarchar(max)		NOT NULL,
	[PostalCode]	nvarchar(max)		NOT NULL,
	[TelNumber]	nvarchar(max)		NULL,
	[FaxNumber]	nvarchar(max)		NULL,
	[MobilNumber]	nvarchar(max)		NOT NULL,
	[IcqNumber]	nvarchar(9)		NULL,
	[SkypeName]	nvarchar(max)		NULL,
	[Jabber]	nvarchar(max)		NULL,
	[Country]	INT			NOT NULL,
	[Question]	INT			NOT NULL,
	[Answer]	nvarchar(max)		NOT NULL,

	CONSTRAINT PK_Profiles PRIMARY KEY CLUSTERED (ProfileId ASC),
	CONSTRAINT FK_Profiles_Questions FOREIGN KEY (Question) REFERENCES dbo.Questions (IdQuestion) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT FK_Profiles_States FOREIGN KEY (Country) REFERENCES dbo.States (IdState) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT FK_Profiles_Users FOREIGN KEY (UserName) REFERENCES dbo.Users (UserName) ON UPDATE CASCADE ON DELETE CASCADE,
)

dbo.States

CREATE TABLE States
(
  IdState	int identity(1,1)	NOT NULL,
  Name		varchar(max)		NULL,
  Flagpic	varchar(max)		NULL,
  Domain	char(3)			NULL,
  CONSTRAINT PK_States PRIMARY KEY CLUSTERED (IdState ASC)
)

dbo.Questions

Create Table dbo.Questions
(
  [IdQuestion]		int IDENTITY(1,1)	NOT NULL,
  [NameQuestion]	nvarchar(255)		NULL,
	
	CONSTRAINT PK_Questions PRIMARY KEY CLUSTERED ([IdQuestion] ASC),
	CONSTRAINT IX_Questions_NameQuestion UNIQUE NONCLUSTERED (NameQuestion ASC),
)

Předem dík za radu a pomoc

Mrkněte příkazy CREATE VIEW a JOIN ;)

Jestli vám jde pouze o spojení tabulek, tak by výsledný pohled mohl vypadat následovně.

CREATE VIEW UsersInformation 
AS
SELECT u.UserId, u.UserName, u.Email, u.Comment, u.Enabled, u.DateCreated, 
	   p.LastUpdate, p.FirstName, p.CompanyName, p.Avatar, p.Street,
	   p.HouseNumber, p.Adress, p.PostalCode, p.TelNumber, p.FaxNumber,
	   p.MobilNumber, p.IcqNumber, p.SkypeName, p.Jabber, p.Answer,
	   s.Name, s.Flagpic, s.Domain,
	   q.NameQuestion
FROM USERS u
INNER JOIN PROFILES p ON p.Username = u.Username
INNER JOIN STATES s ON p.Country = s.IdState
INNER JOIN QUESTIONS q ON p.Question = q.IdQuestion