3

I want to create a random selected cross-joined table which auto increments its own id and joins on it. Let's say my tables looking like this.

Person

Firstname, Lastname 

Hans      | Müller
Joachim   | Bugert

Address
City, Street, StreetNumber
Hamburg | Wandsbeckerstr. | 2
Berlin  | Konradstraße    | 13

Now I want to join the tables with a auto generated ID and they should be random selected. The final table should look like this

ID,Firstname,Lastname, City, Street, StreetNumber
1 |Hans|Bugert|Berlin|Wandsbeckerstr|2
2|Joachim|Müller|Hamburg|Konradstraße | 13

What I already tried or used:

Here I auto-generate the ID where I want to join the tables on

select GENERATED_PERIOD_START as ID FROM SERIES_GENERATE_INTEGER(1,1,10)

The problem is cross join and inner join isn't working for me because it always joins everything with everything or its not joining on the same ID.

SELECT Person."Firstname", Person."Lastname", Address."City",Address."Street", Address."StreetNumber"

FROM 
(   select GENERATED_PERIOD_START as ID FROM SERIES_GENERATE_INTEGER(1,1,10)

) autoGenID
inner JOIN
(select "Firstname" ,"Lastname" FROM Person ORDER BY RAND()) Person

inner JOIN 
(select "City", "Street", "StreetNumber", FROM Address  ORDER BY RAND()) Address

JOIN ON autoGenID."ID"=?????

Here is my problem I can't just select random data and select that on my auto generated ID.

Thanks for your help or ideas how to solve this!

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Schwenk
  • 225
  • 2
  • 13

1 Answers1

4

I think you want:

SELECT p."Firstname", p."Lastname", a."City", a."Street", a."StreetNumber"
FROM (SELECT p.*,
             ROW_NUMBER() OVER (ORDER BY RAND()) as seqnum
      FROM Person p
     ) p JOIN
     (SELECT a.*,
             ROW_NUMBER() OVER (ORDER BY RAND()) as seqnum
      FROM Address a
     ) a
     ON p.seqnum = a.seqnum;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786