Simple Question (Tables) - 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

Simple Question (Tables)

Author

Topic

gigli92
Starting Member

5 Posts

Posted - 2011-07-14 : 05:35:43

Hello Experts,
I'm a newbie with SQL and I have a Q..

I made 2 Temp. Tables.
Each has 25 Rows.(DateValue)

I want to combine this 2 tables in a third table..
First Table is [From]
Second Table is [To]

I want to get it like this:

From| To |
1111|1111
2222|2222
3333|3333

I use this simple Query

Create Table #T3
(
[From] Datetime
,[To] Datetime
)

INSERT Into #T3
SELECT Distinct #T1.[From], #T2.[To]
From #T1,#T2
Where #T1.[From] is not null
And #T2.[To] is not null

Select * from #T3

Drop Table #T3
Drop Table #T2
Drop Table #T1

But my results are like this
From| To |
1111|1111
1111|2222
1111|3333
2222|1111
2222|2222
2222|3333

Any help ?
THANKS !

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-14 : 05:42:30

INSERT Into #T3
SELECT Distinct #T1.[From], #T2.[To]
From #T1 a inner join #T2 b
ON a.From = b.To
Where #T1.[From] is not null
And #T2.[To] is not null

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-14 : 05:47:49

quote:

From #T1,#T2
Where #T1.[From] is not null
And #T2.[To] is not null

This is a Cross Join. It means join every record from table 1 to every record of table 2.

You probably want a Left Join or an Inner Join, but you will need to specify what the relationship is:

All T1 records plus T2 where T2 is related to T1:

From #T1 A
Left Join #T2 B
On A.SomeCol = B.SomeCol

Or noly cases where T1 and T2 are related:

From #T1 A
Inner Join #T2 B
On A.SomeCol = B.SomeCol

Corey

I Has Returned!!

gigli92
Starting Member

5 Posts

Posted - 2011-07-14 : 05:54:19

I have written a mistake,
The From and To Dates are different values..
But the sortorder is correct, i just want to combine these two tables next to eachother

gigli92
Starting Member

5 Posts

Posted - 2011-07-14 : 05:56:37

INSERT INTO #T3 SELECT #T1.[From] , MIN(#T2.[To]) FROM #T1 JOIN #T2 ON #T1.[From] < #T2.[To] GROUP BY #T1.[From]

This one solved my problem. THANKS ANYWAY !

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-14 : 06:01:55

[code]
;with T1 As (
Select *, rId = ROW_NUMBER() Over(Order By SomeCol1) From #T1 Where [From] is not null
), T2
Select *, rId = ROW_NUMBER() Over(Order By SomeCol2) From #T2 Where [To] is not null
)

Select [From], [To]
From T1 A
Inner Join T2 B
On A.rId = B.rId
[/code]

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources