Table joining - 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

General SQL Server Forums

New to SQL Server Programming

Table joining

Author

Topic

daveybohh
Starting Member

5 Posts

Posted - 2011-11-22 : 08:48:28

Hi there,
I am most definitely a bit of a rookie with SQL and have been pulling my hair out regarding this for quite a while.... I have looked on the net and in various SQL books but still can`t figure it out, although I am sure it is quite simple...

I have 2 databases, DB01 and DB02. I also have 2 tables, Table 1 and Table 2. These tables have the same name in both databases.
All the tables contain a column of “tstamp”, a time stamp in unix form.

I would like my query to return various columns from various tables. I tried:

SELECT DB01.dbo.Table1.tstamp, DB01.dbo.Table1.fld7,DB01.dbo.Table2.fld13
FROM DB01.dbo.Table1, DB01.dbo.Table2
WHERE DB01.dbo.Table2.tstamp = DB01.dbo.Table1.tstamp
Union
SELECT DB02.dbo.Table1.tstamp, DB02.dbo.Table1.fld7,DB02.dbo.Table2.fld13
FROM DB02.dbo.Table1, DB02.dbo.Table2
WHERE DB02.dbo.Table2.tstamp = DB02.dbo.Table1.tstamp
Order by tstamp

However, this combines the columns together, with 2 entries for every timestamp. What I really require is one timestamp entry, and 4 columns - DB01.dbo.Table1.fld7, DB01.dbo.Table2.fld13, DB02.dbo.Table1.fld7 and DB02.dbo.Table2.fld13.

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:39:32

i think you need only this then


SELECT DB01.dbo.Table1.tstamp, DB01.dbo.Table1.fld7,DB01.dbo.Table2.fld13,DB02.dbo.Table1.fld7,DB02.dbo.Table2.fld13
FROM DB01.dbo.Table1, DB01.dbo.Table2
WHERE DB01.dbo.Table2.tstamp = DB01.dbo.Table1.tstamp
Order by DB01.dbo.Table1.tstamp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daveybohh
Starting Member

5 Posts

Posted - 2011-11-22 : 09:58:23

Thanks for the quick reply visakh16.

I tried this but I get the error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DB02.dbo.Table1.fld7" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DB02.dbo.Table2.fld13" could not be bound.

Again, I have no idea what this error is caused by. Any help appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 12:58:39


SELECT DB01.dbo.Table1.tstamp, DB01.dbo.Table1.fld7,DB01.dbo.Table2.fld13, DB02.dbo.Table1.fld7,DB02.dbo.Table2.fld13
FROM DB01.dbo.Table1, DB01.dbo.Table2, DB02.dbo.Table1, DB02.dbo.Table2
WHERE DB02.dbo.Table2.tstamp = DB02.dbo.Table1.tstamp
and DB01.dbo.Table2.tstamp = DB01.dbo.Table1.tstamp
and DB01.dbo.Table2.tstamp = DB02.dbo.Table1.tstamp
Order by DB01.dbo.Table1.tstamp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daveybohh
Starting Member

5 Posts

Posted - 2011-11-23 : 04:00:43

Thanks again for your response visakh16. I really appreciate your help here.

I am still getting an error:

Msg 1013, Level 16, State 1, Line 1
The objects "DB02.dbo.Table1" and "DB01.dbo.Table1" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Will I have to use "AS" here? And if so, how?! Thanks again. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-23 : 05:05:30

[code]
SELECT t1.tstamp, t1.fld7,t2.fld13, t3.fld7,t4.fld13
FROM DB01.dbo.Table1 t1, DB01.dbo.Table2 t2, DB02.dbo.Table1 t3, DB02.dbo.Table2 t4
WHERE t4.tstamp = t3.tstamp
and t2.tstamp = t1.tstamp
and t2.tstamp = t3.tstamp
Order by t1.tstamp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daveybohh
Starting Member

5 Posts

Posted - 2011-11-23 : 05:18:58

Superb! Thanks for this. It works a treat. The actual query I need is a bit more complex but now I have the basics to go and work with.

Thanks for your time!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-23 : 06:01:41

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources