joins with table names in other tables

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

Transact-SQL (2000)

joins with table names in other tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-02 : 08:13:44

brian writes "I need to create a killer join querry and am having all kinds of trouble figuring out if it is even possible.

I have a table called Devices, that stores multiple types of Devices, as kept track of in a DeviceTypes table. Each type of device reports different data. A device is something out in the real world that reports time stamped data back to the database. For example, one might measure temperature, and another measure energy usage.

One column in the Devices table is called DataTable char(30) and stores the name of the table the data for that device is stored. So Each data table has a column called ReadingTime (datetime primary key) and then some number of columns that contain the data based on device type.

Now there is one more table of interest, AlarmsPending, which holds alarms to be emailed after a configured delay, in need of attention by a real person. Columns in the table are DeviceID, AlarmTime, and DataName, which is the name of the column in the data table that holds the value in an alarm state.

I would like to run a query that involves all the alarms and gets the details for it to be emailed. I can do this with a cursor in a sproc, but I was thinking it would be much faster if I could just do a query. Plus I can use it in many places that are similar.

I want a record set of every alarm pending to include the value in the data table.

Here is a sample set of data followed by a record set the query should return

Devices
DeviceID DataTable DeviceTypeID
1 datatable1 1
2 datatable2 1
3 datatable3 1
4 datatable4 2
5 datatable5 2

DeviceTypes
DeviceTypeID Description CreateDataTablePrototype
1 Temperature 'create table datatable*** (ReadingTime datetime primary key, temp1 int,temp2 int,temp3 int,temp4 int)'
2 Energy 'create table datatable*** (ReadingTime datetime primary key, kw int, kvar int, input1 int, input2 int)'

AlarmsPending
AlarmID DeviceID AlarmTime DataName
1 1 1/1/2004 12:30:00 PM temp1
2 1 1/1/2004 12:35:00 PM temp3
3 3 1/1/2004 12:35:00 PM temp4
4 4 1/1/2004 12:40:00 PM kw
5 5 1/1/2004 12:40:00 PM kw
6 2 1/1/2004 12:45:00 PM temp2

datatable1
ReadintTime temp1 temp2 temp3 temp4
...
1/1/2004 12:25:00 PM 80 70 74 70
1/1/2004 12:30:00 PM 81 70 78 70
1/1/2004 12:35:00 PM 81 70 78 70
1/1/2004 12:40:00 PM 82 70 79 70
1/1/2004 12:45:00 PM 82 70 79 70

datatable2
ReadintTime temp1 temp2 temp3 temp4
...
1/1/2004 12:25:00 PM 70 70 70 70
1/1/2004 12:30:00 PM 70 71 71 70
1/1/2004 12:35:00 PM 70 72 70 70
1/1/2004 12:40:00 PM 70 74 70 70
1/1/2004 12:45:00 PM 70 80 70 70

datatable3
ReadintTime temp1 temp2 temp3 temp4
...
1/1/2004 12:25:00 PM 70 70 70 70
1/1/2004 12:30:00 PM 70 69 71 73
1/1/2004 12:35:00 PM 70 70 72 76
1/1/2004 12:40:00 PM 70 70 73 78
1/1/2004 12:45:00 PM 70 70 73 81

datatable4
ReadintTime kw kvar input1 input2
...
1/1/2004 12:25:00 PM 185 48 1 0
1/1/2004 12:30:00 PM 183 47 1 0
1/1/2004 12:35:00 PM 179 49 1 0
1/1/2004 12:40:00 PM 210 69 1 0
1/1/2004 12:45:00 PM 215 72 1 0

datatable5
ReadintTime kw kvar input1 input2
...
1/1/2004 12:25:00 PM 105 16 1 0
1/1/2004 12:30:00 PM 103 16 1 0
1/1/2004 12:35:00 PM 104 15 1 0
1/1/2004 12:40:00 PM 135 21 1 0
1/1/2004 12:45:00 PM 134 20 1 0

Query and resulting record set

select ap.AlarmID, ap.Devic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-03 : 19:46:27

You have a very bad design here... Very bad.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources