CODEWITHSUNDEEP
sqlsql-server
Gigli
Gigli

Reputation: 11

SQL Server Simple Problem

EDIT: I have written it a bit wrong gill change my Q

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]... Both tables have different values

I want to get it like this:

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

etc..

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

It multiplies the first field with the second wich gives me alot more records back..

Any help ? THANKS !

Upvotes: 0

Views: 80

Answers (2)

BugFinder
BugFinder

Reputation: 17868

It would do. It will insert a copy of table 2 for each line of table 1, as you didnt say how for it to work out how to extract what you want.

Now, assuming from and to are the same.. you can do

INSERT  Into #T3
SELECT Distinct #T1.[From], #T2.[To]
From #T1 left join #T2 on #T1.[From]=#T2.[To]
Where #T1.[From] is not null

if this isnt how you mean (although having same value in both columns would seem counter productive in that sense), what other fields have you got and how would you tie the lines together.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

After the OP's edit

This may work as you want (which is not entirely clear):

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

Using

FROM T1, T2

results in all combinations or rows of T1 and T2. It's called a cross product and (properly) used with CROSS JOIN, like this:

FROM T1 CROSS JOIN T2

When you want to join the two tables based on a condition (and not get the cross product), you use a JOIN or INNER JOIN (these two are same thing):

FROM T1 JOIN T2
  ON T1.[From] = T2.[To]

will get you all rows combinations where T1.From matches T2.To (on equality). I suppose you wanted to match every row of T1 with the row of T2 where T2.To was just larger than T1.From so I used the "smaller than" < operator instead of the "equality" = operator.

The GROUP BY and MIN() were added to get only the one with smallest T2.To from those rows.

Upvotes: 1

Related Questions