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

User · Answer

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

User · Answer

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

User · Answer

Try this   SELECT ProductName NumberofProducts  NumberofProductssold    FROM table1       JOIN table2      ON table1 ProductName   table2 ProductName

User · Answer

How about   select         col1           col2           null col3           null col4      from Table1 union all select          null col1           null col2          col4 col3           col5 col4      from Table2

User · Answer

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

User · Answer

Try this   GET THE RECORD FOR CURRENT MONTH  LAST MONTH AND ALL TIME AND MERGE THEM INTO SINGLE ARRAY   analyticsData    this- gt user- gt getMemberInfoCurrentMonth  userId    analyticsData1    this- gt user- gt getMemberInfoLastMonth  userId    analyticsData2    this- gt user- gt getMemberInfAllTime  userId                                foreach   analyticsData2 as  arr                            foreach   analyticsData1 as  arr1                if   arr- gt fullname     arr1- gt fullname                     arr- gt last send count    arr1- gt last send count                  break               else                   arr- gt last send count   0                                  foreach   analyticsData as  arr2                if   arr- gt fullname     arr2- gt fullname                     arr- gt current send count    arr2- gt current send count                  break               else                   arr- gt current send count   0                                    echo   lt pre gt        print r  analyticsData2  die

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to combine results of two queries into a single dataset

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql