CODEWITHSUNDEEP
sqlsql-server
souki
souki

Reputation: 1385

How to combine the columns of two tables that don't have any column in common?

I have two tables, Car :

Brand Miles
Some brand 120
Another brand 300

And NewNumberPlate :

NumberPlate
361 2360
418 2711

These two tables always have the same number of rows. I would like to find a way to combine them, in a way to assign a new and unique numberplate to each car like below :

Brand Miles NumberPlate
Some brand 120 361 2360
Another brand 300 418 2711

I have tried the following :

SELECT Car.Brand, Car.Miles, NewNumberPlate.NumberPlate INTO #newCars FROM Car, NewNumberPlate;

But the result I'm getting is the following :

Brand Miles NumberPlate
Some brand 120 361 2360
Another brand 300 361 2360
Some brand 120 418 2711
Another brand 300 418 2711

Which does not work since I want the number plates to be unique. Any advice ? Thanks !

Upvotes: 2

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You cannot control the ordering, unless a column specifies that. But you can use row_number() to generate a key for joining:

select c.brand, c.miles, nn.NumberPlate
from (select c.*,
             row_number() over (order by (select null)) as seqnum
      from car c
     ) c join
     (select nn.*,
             row_number() over (order by (select null)) as seqnum
      from NewNumberPlate nn
     ) nn
     on nn.seqnum = c.seqnum

Upvotes: 1

Related Questions