Joining two tables together with similar Timestamp

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)

Joining two tables together with similar Timestamp

Author

Topic

fiesta_donald
Starting Member

4 Posts

Posted - 2003-12-05 : 12:38:50

I'm trying to join one table, Y, to another table, Z, where they have SIMILAR Timestamps. The timestamp is a "datetime" data type and contains the time the data was recorded. Also, this query will be used to create a view needed for a report.

The database tables are as follows:

table Y
02NG1_TOT
02NG2_TOT
TIMESTAMP

table Z
03NG1_TOT
03NG2_TOT
TIMESTAMP

What I need is to have the result be a view (both tables combined together with similar timstamps) with the structure like

TIMESTAMP
02NG1_TOT
02NG2_TOT
03NG1_TOT
03NG2_TOT

How can I use the JOIN command to accomplish this?

Thank you,

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-05 : 12:42:17

SELECT Column1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.SomeDateColumn = t2.SomeDateColumn

If you don't want to join on the time portion of the column, then do this:

SELECT Column1
FROM Table1 t1
INNER JOIN Table2 t2 ON CONVERT(VARCHAR(50), t1.SomeDateColumn, 101) = CONVERT(VARCHAR(50), t2.SomeDateColumn, 101)

Also, TIMESTAMP is different from DATETIME. SQL Server has a data type called TIMESTAMP. In Yukon, I hear it will be renamed to ROWVERSION.

Tara

fiesta_donald
Starting Member

4 Posts

Posted - 2003-12-05 : 14:58:49

I tried the select statement
SELECT Column1 FROM Table1 t1
INNER JOIN Table2 t2 ON CONVERT(VARCHAR(50), t1.SomeDateColumn, 101) = CONVERT(VARCHAR(50), t2.SomeDateColumn, 101)
with the form
SELECT "02NG1_TOT" FROM Y t1
INNER JOIN Z t2 ON CONVERT(VARCHAR(50), t2.TIMESTAMP, 101) = CONVERT(VARCHAR(50), t2.TIMESTAMP, 101).

I only got a table with 02NG1_TOT. No other columns were returned. What went wrong?

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-05 : 15:00:53

You need to add the columns to the SELECT statement. Why the use of double quotes around your column name? Don't use double quotes.

SELET 02NG1_TOT, 02NG2_TOT...

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-05 : 17:48:33

quote:
What I need is to have the result be a view (both tables combined together with similar timstamps) with the structure like

TIMESTAMP
02NG1_TOT
02NG2_TOT
03NG1_TOT
03NG2_TOT

I am reading this as a UNION ??

SELECT value
FROM y
UNION 
SELECT value
FROM z

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources