CODEWITHSUNDEEP
sql-server-2012
John
John

Reputation: 97

Select data from multiple table without join sql

I have 2 SQL table.Table A and Table B. Both of this table have 10000 records respectively.

In Table A have 3 Column=>

ColumnA,ColumnB,ColumnC

In Table B have 3 Column=>

ColumnD,ColumnE,ColumnF

My Requirement is to select ColumnA and ColumnD with their original record(10000).

My question is how can I select only ColumnA and ColumnD.

The first problem is I cant join this two table because this two table stands as separately.

The second problem is I cant Union this two table because my requirement is to get Two column but when I union, I only get one column with combining two column.

Upvotes: 1

Views: 6440

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522561

You could create an on-the-fly join column via ROW_NUMBER, and then join on that:

WITH cte1 AS (
    SELECT ColumnA, ROW_NUMBER() OVER (ORDER BY ColumnA) rn
    FROM TableA
),
cte2 AS (
    SELECT ColumnD, ROW_NUMBER() OVER (ORDER BY ColumnD) rn
    FROM TableB
)

SELECT t1.ColumnA, t2.ColumnD
FROM cte t1
INNER JOIN cte t2
    ON t1.rn = t2.rn;

Of course, this would just pair the 10K records using the arbitrary orderings in the A and D columns. If you have some specific logic for how these two columns should be paired up, then let us know. The bottom line is that you can't easily get away from the concepts of join or union to bring these two columns together.

Upvotes: 1

Related Questions