A complex Query - 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 2005 Forums

Transact-SQL (2005)

A complex Query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-05-20 : 14:44:23

Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.

now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 14:53:05

Why have you designed your system this way?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 14:59:37

quote:
Originally posted by kamii47

Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.

now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

Why are you creating a new table on addition of each category? what will table consists of?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-20 : 14:59:45

This is the wrong approach.

Use this approach

Table1_MyCategories
CatID CATNAME
1 Red Images
2 Blue Images
3 Green Images

Table2_Images
ImageID CATID ImageName
1 2 MyBlueImage1.jpg
2 1 MyREDImage1.jpg
3 1 MyREDImage2.jpg
4 1 MyREDImage3.jpg
5 3 MyGreenImage1.jpg

Then when you want to get all the information for a specific category just use

Select * from Table2_Images a where a.CatID = .... (i.e. 1,2,3)

Please clarify if this is not what you are looking for. I do not see any reasont why you would want to create separate tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources