How can I join two tables apropreately in (My)SQL?

I need to join two tables together. I know, that there’s a JOIN in MySQL, but those results I’ve got weren’t that good for my purpose.

Example: 
Say, I have two tables:
a = 
{[1, 2]} 
{[3, 4]} 

b = 
{[5, 6]} 
{[7, 8]}

When I now join them using the JOIN-command in MySQL, the result is somewhat like that:

result = 
{[1, 2], [5, 6]}  
{[3, 4], [5, 6]}  
{[1, 2], [7, 8]}  
{[1, 2], [7, 8]}

but what I need is something like

result =  
{[1, 2], [5, 6]} 
{[3, 4], [7, 8]}

If anyone has any idea how to do that with a rather simple SQL-script, I would be very thankful 😀

Greetings 🙂

Answer

You don’t have a join key. You seem to want the data “side-by-side”.

You can do this using row_number() to generate a join key:

select a.*, b.*
from (select a.*,
             row_number() over () as seqnum
      from a
     ) a join
     (select b.*,
             row_number() over () as seqnum
      from b
     ) b
     on a.seqnum = b.seqnum;

Note: This is not guaranteed to preserve the ordering of the rows in the tables. It will just put the values side-by-side.

In older versions, you can use variables for this purpose:

select a., b. from (select a., (@rna := @rna + 1) as seqnum from a cross join (select @rna := 0) params ) a join (select b., (@rnb := @rnb + 1) as seqnum from b cross join (select @rnb := 0) params ) b on a.seqnum = b.seqnum;

Advertisement

Answer