Using SQL Server for Logging - 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)

Using SQL Server for Logging

Author

Topic

seanmc602
Starting Member

1 Post

Posted - 2010-06-30 : 14:02:51

Hi I am very new to SQL and I am currently working with SQL Server 2005 Studio Express. I have a database set up so that there is a daily list of values that get logged into it every 3 minutes. Basically, I have a program that makes all of the tables needed and fills out all of the information, but they are all the same table (same columns) just different data. This program has one table for every hour and I am looking for a way to setup an interactive query command so the user can filter these logs by let's say, "I want the entries of 'Value' = 1 for some date/time range."

I tried doing this by simply INNER JOIN the tables and setting the 'Value' columns equal and filtering them by searching for one of those table's column = 1. I want it so that I query all of the tables for every row in which 'Value' = 1. Everytime I try this I get the Cartesian product and I don't know why.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-30 : 16:10:54

Post the query that you have? DDL and sample data would also help a fair bit.

--
Gail Shaw
SQL Server MVP

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-06-30 : 16:57:40

here is one way of doing it if all the tables are identical.

;with cte (
Select x, y, z from table 1
UNION ALL
Select x, y, z from table 2
UNION ALL
Select x, y, z from table 3
)
SELECT * FROM cte
WHERE 'Value' = 1

or a sub select
SELECT * FROM (
Select x, y, z from table 1
UNION ALL
Select x, y, z from table 2
UNION ALL
Select x, y, z from table 3)

WHERE 'Value' = 1

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources