CODEWITHSUNDEEP
sql
user4109
user4109

Reputation: 153

sql to combine two unrelated tables into one

I have tables

table1

col1    col2    
a       b
c       d

and table2

mycol1  mycol2
e           f
g           h
i           j
k           l

I want to combine the two tables, which have no common field into one table looking like:

table 3

col1    col2    mycol1  mycol2
a           b   e   f
c           d   g   h
null    null    i   j
null    null    k   l

ie, it is like putting the two tables side by side.

I'm stuck! Please help!

Upvotes: 14

Views: 15002

Answers (3)

jmilloy
jmilloy

Reputation: 8365

Option 1: Single Query

You have to join the two tables, and if you want each row in table1 to match to only one row in table2, you have to restrict the join somehow. Calculate row numbers in each table and join on that column. Row numbers are database-specific; here is a solution for mysql:

SELECT
    t1.col1, t1.col2, t2.mycol1, t2.mycol2
FROM
    (SELECT col1, col2, @t1_row := t1_row + 1 AS rownum FROM table1, (SELECT @t1_row := 0) AS r1) AS t1
    LEFT JOIN
    (SELECT mycol1, mycol2, @t2_row := t2_row + 1 AS rownum FROM table2, (SELECT @t2_row := 0) AS r2) AS t2
    ON t1.rownum = t2.rownum;

This assumes table1 is longer than table2; if table2 is longer, either use RIGHT JOIN or switch the order of the t1 and t2 sub-selects. Also note that you can specify the order of each table separately using an ORDER BY clause in the sub-selects.

(See select increment counter in mysql)

Option 2: Post-processing

Consider making two selects, and then concatenating the results with your favorite scripting language. This is a much more reasonable approach.

Upvotes: 2

David Söderlund
David Söderlund

Reputation: 998

It's really good if you put in a description of why this problem needs to be solved. I'm guessing it is just to practice sql syntax?

Anyway, since the rows don't have anything connecting them, we have to create a connection. I chose the ordering of their values. Also since they have nothing connecting them that also begs the question on why you would want to put them next to each other in the first place.

Here is the complete solution: http://sqlfiddle.com/#!6/67e4c/1

The select code looks like this:

WITH rankedt1 AS
(
  SELECT col1
  ,col2
  ,row_number() OVER (order by col1,col2) AS rn1
  FROM table1
  )
,rankedt2 AS 
(
  SELECT mycol1
  ,mycol2
  ,row_number() OVER (order by mycol1,mycol2) AS rn2
  FROM table2
  )

SELECT
col1,col2,mycol1,mycol2
FROM rankedt1
FULL OUTER JOIN rankedt2
  ON rn1=rn2

Upvotes: 2

zimdanen
zimdanen

Reputation: 5626

Get a row number for each row in each table, then do a full join using those row numbers:

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.

Upvotes: 17

Related Questions