CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
CodeNinja
CodeNinja

Reputation: 3278

How to combine results of two queries into a single dataset

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. )

Upvotes: 52

Views: 308814

Answers (10)

Roland
Roland

Reputation: 5232

Old question, but where others use JOIN to combine unrelated queries to rows in one table, this is my solution to combine unrelated queries to one row, e.g:

select 
  (select count(*) c from v$session where program = 'w3wp.exe') w3wp,
  (select count(*) c from v$session) total,
  sysdate
from dual;

which gives the following one-row output:

W3WP   TOTAL  SYSDATE
-----  -----  -------------------
   14    290  2020/02/18 10:45:07

(which tells me that our web server currently uses 14 Oracle sessions out of the total of 290 sessions; I log this output without headers in an sqlplus script that runs every so many minutes)

Upvotes: 3

www
www

Reputation: 4391

Try this:

SELECT ProductName,NumberofProducts ,NumberofProductssold
   FROM table1 
     JOIN table2
     ON table1.ProductName = table2.ProductName

Upvotes: 0

Gojira
Gojira

Reputation: 3051

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...

Upvotes: 86

scorpionsrk7
scorpionsrk7

Reputation: 15

Try this:

GET THE RECORD FOR CURRENT_MONTH, LAST_MONTH AND ALL_TIME AND MERGE THEM INTO SINGLE ARRAY

$analyticsData = $this->user->getMemberInfoCurrentMonth($userId);
$analyticsData1 = $this->user->getMemberInfoLastMonth($userId);
$analyticsData2 = $this->user->getMemberInfAllTime($userId);                        

    foreach ($analyticsData2 as $arr) {                
        foreach ($analyticsData1 as $arr1) {
            if ($arr->fullname == $arr1->fullname) {
                $arr->last_send_count = $arr1->last_send_count;
                break;
            }else{
                $arr->last_send_count = 0;
            }
        }
        foreach ($analyticsData as $arr2) {
            if ($arr->fullname == $arr2->fullname) {
                $arr->current_send_count = $arr2->current_send_count;
                break;
            }else{
                $arr->current_send_count = 0;
            }
        }
    }
    echo "<pre>";
    print_r($analyticsData2);die;

Upvotes: -6

Roney Michael
Roney Michael

Reputation: 3994

If you mean that both ProductName fields are to have the same value, then:

SELECT a.ProductName,a.NumberofProducts,b.ProductName,b.NumberofProductsSold FROM Table1 a, Table2 b WHERE a.ProductName=b.ProductName;

Or, if you want the ProductName column to be displayed only once,

SELECT a.ProductName,a.NumberofProducts,b.NumberofProductsSold FROM Table1 a, Table2 b WHERE a.ProductName=b.ProductName;

Otherwise,if any row of Table1 can be associated with any row from Table2 (even though I really wonder why anyone'd want to do that), you could give this a look.

Upvotes: 4

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

This is what you can do. Assuming that your ProductName column have common values.

SELECT 
     Table1.ProductName, 
     Table1.NumberofProducts, 
     Table2.ProductName, 
     Table2.NumberofProductssold
FROM Table1
INNER JOIN Table2
ON Table1.ProductName= Table2.ProductName

Upvotes: 0

Kaf
Kaf

Reputation: 33839

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 |

Upvotes: 41

RandomUs1r
RandomUs1r

Reputation: 4188

Load each query into a datatable:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=143

load both datatables into the dataset:

http://msdn.microsoft.com/en-us/library/aeskbwf7%28v=vs.80%29.aspx

Upvotes: 0

David T. Macknet
David T. Macknet

Reputation: 3172

The problem is that unless your tables are related you can't determine how to join them, so you'd have to arbitrarily join them, resulting in a cartesian product:

select Table1.col1, Table1.col2, Table2.col3, Table2.col4
from Table1
cross join Table2

If you had, for example, the following data:

col1  col2
a     1
b     2

col3  col4
y     98
z     99

You would end up with the following:

col1  col2  col3  col4
a     1     y     98
a     1     z     99
b     2     y     98
b     2     z     99

Is this what you're looking for? If not, and you have some means of relating the tables, then you'd need to include that in joining the two tables together, e.g.:

select Table1.col1, Table1.col2, Table2.col3, Table2.col4
from Table1
inner join Table2
on Table1.JoiningField = Table2.JoiningField

That would pull things together for you into however the data is related, giving you your result.

Upvotes: 4

Jodrell
Jodrell

Reputation: 35746

How about,

select
        col1, 
        col2, 
        null col3, 
        null col4 
    from Table1
union all
select 
        null col1, 
        null col2,
        col4 col3, 
        col5 col4 
    from Table2;

Upvotes: 12

Related Questions