CODEWITHSUNDEEP
asp.netdatabasesql-server-cewebmatrix
user1173468
user1173468

Reputation: 1

Querying multiple tables having the same column name

I have about 20 tables in one database, with the same column names i.e.

TeacherName,Subject,Class,etc...

Now I want to query all the tables at once to display all the rows having teacher name say : "John Travolta".

How can I do that? I saw in some posts UNION would be a way to do it, but since there are 20 or even more tables, I want if there is a better way to do it...

Btw I am using SQL SERVER COMPACT in WEBMATRIX....

Upvotes: 0

Views: 656

Answers (2)

Oybek
Oybek

Reputation: 7243

You'll have to use Union if you want to do it in a pure SQL manner.

There are some tricks using EF, inheritance and a few configuration that will give you an access to the several tables with the same columns via one collection. But I'm not an expert with this approach.

Further you could do a view combining all columns from all your tables. However SQL Server CE doesn't support views. You can do so if you'll migrate your db at least to SQL Server Express.

And finally you could generate your SQL statement in your asp.net application using stringbuilder.

Upvotes: 0

Sagi
Sagi

Reputation: 1612

Can't think of a a different way to do it except union of all query results for the tables. The question you really need to ask yourself is, why you have so many tables with same data stored in all of them? I think that this is a bad practice and you should have one table with a field which would separate them, or even extension tables to the different type of rows. But the bottom line - Every data which represents the same information should be stored in one table.

Upvotes: 2

Related Questions