how to display unrelated data - 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

SQL Server 2000 Forums

Transact-SQL (2000)

how to display unrelated data

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2004-07-02 : 14:54:38

declare @table1 table(a int)
declare @table2 table(b datetime)

insert @table1 select 1 union all select 2 union all select 3
insert @table2 select getdate() union all select getdate()+1 union all select getdate()+2

select*from @table1 cross join @table2 --3*3=9
/* looks like
1 2004-07-02 14:09:04.900
2 2004-07-02 14:09:04.900
3 2004-07-02 14:09:04.900
1 2004-07-03 14:09:04.900
2 2004-07-03 14:09:04.900
3 2004-07-03 14:09:04.900
1 2004-07-04 14:09:04.900
2 2004-07-04 14:09:04.900
3 2004-07-04 14:09:04.900

I need any combination from both tables for a total of 3 rows with only distinct values from both tables
ex:
1 2004-07-03 14:09:04.900
2 2004-07-02 14:09:04.900
3 2004-07-04 14:09:04.900

the only way I can think of right now is to create tables with
an additional identity field and join on it
but I am pretty sure there is a better way*/

declare @table3 table(a int, idf int identity(1,1))
declare @table4 table(b datetime, idf int identity(1,1))

insert @table3(a) select 1 union all select 2 union all select 3
insert @table4(b)select getdate() union all select getdate()+1 union all select getdate()+2

select a, b from @table3 a join @table4 b on a.idf=b.idf -- 3 row only

thank you

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-03 : 07:52:15

Can you provide another example or elaborate on what exactly you are trying to accomplish. I believe you have not recevied a response because it is unclear what you are trying to accomplish.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-03 : 09:53:39

This works in SQL Server 2005 (well, it certainly does in the current Express beta).
Replacing NULL with NEWID() works too to give you a random(ish) ordering rather than a merely non-deterministic one.


SELECT a, b
FROM (
    SELECT a, ROW_NUMBER() OVER (ORDER BY NULL) AS r1
    FROM @table1
  ) AS T1
INNER JOIN (
    SELECT b, ROW_NUMBER() OVER (ORDER BY NULL) AS r2
    FROM @table2
  ) AS T2
  ON r1 = r2

Edit: Hmm, now there's an interesting thing. Despite being a row numbering, the optimizer doesn't take into account that r1 and r2 will be uniquely valued, and therefore can be joined efficiently: instead, if the tables are large it uses a nested loop join and so goes quadratic. (Doh!) Put a MERGE hint in the join (you'll get a many-to-many merge, but it'll still be miles better than a nested loop).

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-04 : 06:08:51

How about:

select a,b from
(
select a, b,
(select count(1) from (select * from @table1 cross join @table2) a2 where a2.b <= a1.b and a1.a = a2.a) ct1,
(select count(1) from (select * from @table1 cross join @table2) a2 where a2.a <= a1.a and a1.b = a2.b) ct2
from (select * from @table1 cross join @table2) a1
) x
where ct1 = ct2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources