Reg using of Subquery - 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)

Reg using of Subquery

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 06:52:05

Hi All,

I am having 3 tables Artist,Show, GAllery. I have to write a query to get the Gallery name which has conducted max shows over a period of one month

Artist table has ArtistId,NAme,Region
Show has Artistid,Galleryid,showdate
Gallery has Galleryid,name,region

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 07:19:44

Somthing like this


Select G.Name From Gallery G Inner Join 
(Select Top 1 Count(1),Galleryid From Show Where ShowDate between DateAdd(dd,-30,GetDate() And DateAdd(dd,1,Getdate()
Group by Galleryid 
order by 1 Desc) as f 
on f.Galleryid = g.Galleryid

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 07:36:08

Whats is f.Galleryid? how did u get this value. Not Clear

quote:

Originally posted by chiragkhabaria

Somthing like this


Select G.Name From Gallery G Inner Join 
(Select Top 1 Count(1),Galleryid From Show Where ShowDate between DateAdd(dd,-30,GetDate() And DateAdd(dd,1,Getdate()
Group by Galleryid 
order by 1 Desc) as f 
on f.Galleryid = g.Galleryid

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 08:08:44

quote:

Whats is f.Galleryid? how did u get this value. Not Clear

"f" is the Alias of the derived table and GalleryID is the column name of the derived table.

is the output as expected?

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 08:15:42

Select G.Name From Gallery G Inner Join
(Select Top 1 Count(Date1),Galler_Fk From Show Where Date1 between '05/01/2004' and '07/31/2004'
Group by Galler_FK
order by 1 Desc) as f
on f.Galler_FK = g.Gallery_id

I am getting error
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'f'.

quote:
Originally posted by chiragkhabaria

quote:

Whats is f.Galleryid? how did u get this value. Not Clear

"f" is the Alias of the derived table and GalleryID is the column name of the derived table.

is the output as expected?

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 08:18:13

Opps forget to give the name to the derived table..

Try this


 Select G.Name From Gallery G Inner Join
(Select Top 1 Count(Date1) As Count,Galler_Fk From Show Where Date1 between '05/01/2004' and '07/31/2004'
Group by Galler_FK
order by 1 Desc) as f
on f.Galler_FK = g.Gallery_id

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 08:55:33

Ok Thanks.

quote:
Originally posted by chiragkhabaria

Opps forget to give the name to the derived table..
Try this

Select G.Name From Gallery G Inner Join
(Select Top 1 Count(Date1) As Count,Galler_Fk From Show Where Date1 between '05/01/2004' and '07/31/2004'
Group by Galler_FK
order by 1 Desc) as f
on f.Galler_FK = g.Gallery_id

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources