How to combine results of two queries into a single dataset

Question

I have two queries : Queries Simplified excluding Joins

Query 1 : select ProductName,NumberofProducts (in inventory) from Table1.....; Query 2 : select ProductName, NumberofProductssold from Table2......; 

I would like to know how I can get an output as :

ProductName NumberofProducts(in inventory)  ProductName NumberofProductsSold 

The relationships used for getting the outputs for each query are different. I need the output this way for my SSRS report .

(I tried the union statement but it doesnt work for the output I want to see. )

Answer 1

Here is an example that does a union between two completely unrelated tables: the Student and the Products table. It generates an output that is 4 columns:

select         FirstName as Column1,         LastName as Column2,         email as Column3,         null as Column4     from         Student union select         ProductName as Column1,         QuantityPerUnit as Column2,         null as Column3,         UnitsInStock as Column4     from         Products 

Obviously you'll tweak this for your own environment...

Answer 2

I think you are after something like this; (Using row_number() with CTE and performing a FULL OUTER JOIN )

Fiddle example

;with t1 as (   select col1,col2, row_number() over (order by col1) rn   from table1  ), t2 as (   select col3,col4, row_number() over (order by col3) rn   from table2 ) select col1,col2,col3,col4 from t1 full outer join t2 on t1.rn = t2.rn 

Tables and data :

create table table1 (col1 int, col2 int) create table table2 (col3 int, col4 int)  insert into table1 values (1,2),(3,4)  insert into table2 values (10,11),(30,40),(50,60) 

Results :

|   COL1 |   COL2 | COL3 | COL4 | --------------------------------- |      1 |      2 |   10 |   11 | |      3 |      4 |   30 |   40 | | (null) | (null) |   50 |   60 |