SQL Server Simple Problem

Question

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

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

Any help ? THANKS !

You are not supplying enough information. What is in #T1, #T2? What are the keys? What exactly are you are trying to accomplish? — Blazes
– Blazes, Commented Jul 14, 2011 at 9:45
Change the check to eqality check: Where #T1.[From] = #T2.[To]. And you probably don't need the DISTINCT at all. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 14, 2011 at 9:45
What is the relationship between both these tables (#T1 and #T2), are their any key? — Scorpion
– Scorpion, Commented Jul 14, 2011 at 9:59

ypercubeᵀᴹ · Accepted Answer · 2011-07-14 10:09:54Z

1

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.

edited Jul 14, 2011 at 10:09

answered Jul 14, 2011 at 9:54

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

@cularis: I only figured it after I noticed the OP's edit. The first time, I figured the same with you (only I removed the useless IS NULL checks).

Andriy M · Accepted Answer · 2011-07-14 15:11:55Z

0

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.

edited Jul 14, 2011 at 15:11

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jul 14, 2011 at 9:46

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Collectives™ on Stack Overflow

SQL Server Simple Problem

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related