How to join more than two tables using sql without Common Columns

3
votes

Example:

Table 1:

Col1           Col2
-----------    -------------
A                 1
B                 2
D                 3
E                 4

Table 2:

Col3           Col4
-----------    -------------
A                 7
E                 9
Z                 5

Table 3:

Col5           Col6
-----------       -------------
Y                 8

Expected output:

Col1    Col2    Col3    Col4    Col5     Col6
----    ----    ----    ----    ----     ----
A       1       A       7       Y        8
B       2       E       9       NULL    NULL
D       3       Z       5       NULL    NULL
E       4       NULL    NULL    NULL    NULL

I want the output as shown in the picture.

If there are three tables with columns as Col11, Col2 and Col3, Col4 and Col5 and Col6 then expected output should be as Col1, Col2, Col3, Col4, Col5, Col6 without any joins. It should just Table2 is at right hand side of the Table1 And Table3 should be at right of the Table2. If number of rows don't match then Null values will consume the space. I know the solution for Two tables. But Need SQL query syntax for n number of tables. -Thanks in Advance.

2
sql-server
Please post your solution for two tables, it would be interesting to see. To be honest I'm finding it hard to understand what use this could be... maybe give us some details on your broader requirements too? Remember a DB is not a spreasheet! – beercohol
In this case there "is" a common value - the ROW NUMBER (which should be sufficient information if proceeding with this); and as such it can be joined on once synthesized in derived tables. The ordering from the shown data is the first column in that group (and if this does not hold, then you don't even have ROW NUMBERS available!). However, this is .. a very odd design that may indicate an appropriate KEY or misunderstanding of RA. – user2864740
This is not possible with SQL Sever because there is technically no ordering to the rows unless you specify one. However if you add to your question that you are specifying that Col1, Col3, and Col5 are unique in each table and that is the field to provide the ordering for each... than I can build you a query to do it. I agree with the comment above though... something is probably off in the design if you need to do this. – Brian Pressler

2 Answers

2
votes

Although I can't quite see why you'd want to do this, I've attempted to answer the question as an exercise for my own learning!

Thanks to @user2864740 for the idea of using ROW_NUMBER to synthesize a common value between each table!

Here's the query:

SELECT
  col1,
  col2,
  col3,
  col4,
  col5,
  col6
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY col1) AS Row,
  col1, col2
  FROM table1) T1

  FULL OUTER JOIN

  (SELECT ROW_NUMBER() OVER(ORDER BY col3) AS Row,
  col3, col4
  FROM table2) T2 ON T1.Row = T2.Row

  FULL OUTER JOIN

  (SELECT ROW_NUMBER() OVER(ORDER BY col5) AS Row,
  col5, col6
  FROM table3) T3 ON T1.Row = T3.Row

And a SQL Fiddle demonstrating it: http://sqlfiddle.com/#!3/6c2db/6

0
votes

When you are joining for no reason you must "make something up" in this case use row_number and join on that. Notice this would cut off table2 and table3 results if they had more than table 1, to avoid that change left joins to full outer joins and Cartesian everything. Simplest way to do this is : 

Select Col1, Col2, Col3, Col4, Col5, Col6
From 
(select Col1, Col2, ROW_NUMBER() Over (order by Col1,Col2) 'Rnk1'
From table1 ) t1
left join 
(select Col3, Col4, ROW_NUMBER() Over (order by Col3,Col4) 'Rnk2'
From table1 ) t2
On t1.rnk1 = t2.rnk2
left join 
(select Col5, Col6, ROW_NUMBER() Over (order by Col5,Col6) 'Rnk3'
From table3 ) t3
On t1.rnk1 = t3.rnk3