creating a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

creating a view

Author

Topic

nounabarbie
Starting Member

10 Posts

Posted - 2006-04-13 : 04:17:00

Hi
I have 2 tables:Article and CompleteFile
Article contains the following fields:
ArticleID,Language,Source,Date,Article Title,Article Brief

CompleteFile contains the following fields:
ID,ArticleID,CompleteFile

one Article can have up to 5 CompleteFiles so I want to create a view that returns to me the following fields:

Language, Article Title, Article Brief, Article Source, Date,
ArticleCompleteFile1,ArticleCompleteFile2....ArticleCompleteFile5
e.g if the article has 2 complete files,so the first one will be stored in CompleteFile1 and the second in CompleteFile2

May someone help me out?

Thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 05:47:44

Is this what you have in mind ?

Not a very elegant solution

declare	@Article table
(
	ArticleID	int,
	Language	int,
	Source		int,
	Date		datetime,
	ArticleTitle	varchar(10),
	ArticleBrief	varchar(10)
)

declare	@CompleteFile table
(
	ID		int	identity(1,1),
	ArticleID	int,
	CompleteFile	int
)

insert into @Article
select	1, 100, 100, getdate(), 'Article 1', ''	union all
select	2, 200, 200, getdate(), 'Article 2', ''

insert into @CompleteFile(ArticleID, CompleteFile)
select	1, 11	union all
select	1, 12	union all
select	1, 13	union all
select	2, 21	union all
select	2, 22


select	a.ArticleID, a.ArticleTitle,
	c1.CompleteFile as CompleteFile1, 
	c2.CompleteFile as CompleteFile2, 
	c3.CompleteFile as CompleteFile3, 
	c4.CompleteFile as CompleteFile4
from	@Article a
	inner join @CompleteFile c1
	on	a.ArticleID	= c1.ArticleID
	and	c1.CompleteFile = (select top 1 CompleteFile from @CompleteFile x where x.ArticleID = c1.ArticleID order by x.CompleteFile)
	left join @CompleteFile c2
	on	a.ArticleID	= c2.ArticleID
	and	c2.CompleteFile = (select top 1 CompleteFile from @CompleteFile x where x.ArticleID = c2.ArticleID 
					and	x.CompleteFile	<> c1.CompleteFile
					order by x.CompleteFile)
	left join @CompleteFile c3
	on	a.ArticleID	= c3.ArticleID
	and	c3.CompleteFile = (select top 1 CompleteFile from @CompleteFile x where x.ArticleID = c3.ArticleID
					and	x.CompleteFile	<> c1.CompleteFile
					and	x.CompleteFile	<> c2.CompleteFile
					order by x.CompleteFile)
	left join @CompleteFile c4
	on	a.ArticleID	= c4.ArticleID
	and	c4.CompleteFile = (select top 1 CompleteFile from @CompleteFile x where x.ArticleID = c4.ArticleID
					and	x.CompleteFile	<> c1.CompleteFile
					and	x.CompleteFile	<> c2.CompleteFile
					and	x.CompleteFile	<> c3.CompleteFile
					order by x.CompleteFile)

ArticleID   ArticleTitle CompleteFile1 CompleteFile2 CompleteFile3 CompleteFile4 
----------- ------------ ------------- ------------- ------------- ------------- 
1           Article 1    11            12            13            NULL
2           Article 2    21            22            NULL          NULL