Get Unique Column from table 1 and table 2 in cross join

Question

I am joining two unrelated tables using cross join, which will allow me update third table using the result.

For example.

Table1
  TableId,
  Table1Date

Table2   
  Table2Id

select top 10 t1.Id,t2.Id 't2Id' from table1 t1
Cross join table2 t2

Above query works but as expected, there are duplicate entries of table1

How can I ensure that these 10 result entries have all got different value from t1 and different value from t2?

Also I am looking to use this result to update another table with distinct values

update table 3 
Set table3.t2Id=t3.t2Id
(
   the select statement from above
) t3
where table3.Id=t3.T1Id

Sample Data Results with Cross JOIN. t2Id is right but t1Id repeats. Apologies for bad formatting. i am looking for different values as well in t1Id

t1Id                                    t2Id
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03957DD7-B9B6-4D6A-9F04-84D810B4D50B
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    039EF33A-9CB1-4B0B-95DA-01B68A2494AD
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03A45657-7407-4D9A-8BD1-9011759FD6D0
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03A72BF8-C04C-4F48-82E0-9BF68B285356
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03AF3D6F-D6A3-42CC-B37C-7DC6F306679D
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03C24CD2-9C99-43AD-A80B-9F3139B25356
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03C89916-33A7-487A-8A6A-22364E1A4D90
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03D16609-A7ED-4846-BB39-C04C0A22A8ED
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03DB344B-4CFB-4C0E-BFA1-FB55D93A3C57
02A557A9-FF4F-4B53-9E95-5C0B9D8CACA1    03DE5B67-347E-40FA-B85F-807C05DA2E90

You can check this QA related to your problem http://stackoverflow.com/questions/12932045/cross-join-without-duplicate-combinations — Charlesliam, Apr 05 '17 at 05:24
@Charlesliam none of these work maybe because t1.Id and t2.Id are guid columns. — Mandar Jogalekar, Apr 05 '17 at 05:27
@TimBiegeleisen nope (if i get your q right) as i i am looking for the result in the same format because i will also use the t1.Id result to update third table and to make sure that distinct rows in third table are updated — Mandar Jogalekar, Apr 05 '17 at 05:29
I don't think you get my question right. If I return to you `1,2` and `2,1` is that acceptable, or not? — Tim Biegeleisen, Apr 05 '17 at 05:30
@MandarJogalekar in other words, define what you mean by "Duplicates". maybe show us an example? — Stephen, Apr 05 '17 at 05:31
nope, because second option (2,1) will not update right entry in third table looking for unique 1,unique 2 — Mandar Jogalekar, Apr 05 '17 at 05:31
@MandarJogalekar please add your "Duplicate" results on your post — Stephen, Apr 05 '17 at 05:39
So a value that occurs for one ID in your output can not be used again by either ID or at the same time by the other ID? — toonice, Apr 05 '17 at 05:42
@TimBiegeleisen i have added sample result data.if it helps. — Mandar Jogalekar, Apr 05 '17 at 06:09

score 3 · Accepted Answer · answered Apr 05 '17 at 05:52

3

To get distinct pairs (e.g. pair 1|2 only occurs once at most):

select top 10 
  t1.Id as t1id,
  t2.Id as t2id
from table1 t1
cross join table2 t2
order by newid();

To get distinct values (e.g. pair 1|2 cannot occur with 1|3 or 3|2):

select 
  t1.Id as t1id,
  t2.Id as t2id
from (select top 10 *, row_number() over (order by newid() as rn from table1) t1
join (select top 10 *, row_number() over (order by newid() as rn from table2) t2
  on t1.rn = t2.rn;

answered Apr 05 '17 at 05:52

Thorsten Kettner

89,309
7
49
73

1

You have edited your request saying you want distinct values in t1, too. My first query doesn't guarantee this. It may happen to look so when you try, because of the random ordering bevor picking the ten records, but you may still get duplicates. The second query is guaranteed to avoid duplicates both in t1 and t2 values. – Thorsten Kettner Apr 05 '17 at 06:15

Get Unique Column from table 1 and table 2 in cross join

1 Answers1