How to Merge SQL Query(Help required)

Question

Dear friends, below are my two SQL queries:

select distinct  
    a_bm.DestProvider_ID,
    a_bm.DestCircel_ID,
    convert(datetime,dbo.fnToDate(a_bm.BM_BillFrom),103) as fromdate,
    convert(datetime,dbo.fnToDate(a_bm.BM_BillTo),103) as todate,
    t_rec.TapInRec as BillRecevable,
    t_rec.TapInRec as Billreceied
from Auditdata_BillingMaster a_bm 
inner join TapInRecordMaster t_rec
    on a_bm.DestProvider_ID = t_rec.DestProviderMaster_ID
    and a_bm.DestCircel_ID = t_rec.DestCircelMaster_ID
    and convert(datetime,dbo.fnToDate(a_bm.BM_BillFrom),103)> =  
        convert(datetime,t_rec.Months) 
    and convert(datetime,dbo.fnToDate(a_bm.BM_BillTo),103)<= 
        convert(datetime,t_rec.BillTo)
where a_bm.DestProvider_ID=4
and a_bm.DestCircel_ID=22
and a_bm.typeoffile=1
and convert(datetime,dbo.fnToDate(a_bm.BM_BillFrom),103)>=
    convert(datetime,'6/1/2009') 
and convert(datetime,dbo.fnToDate(a_bm.BM_BillFrom),103)<=
    convert(datetime,'7/30/2009')

 

select Temp_tbl.fromdate from Temp_tbl Temp_tbl
inner join (
    select
        convert(datetime,dbo.fnToDate(BM_BillFrom),103) as a1,
        convert(datetime,dbo.fnToDate(BM_BillTo),103) as b1,
        count(*) as c1,
        am_bm.DestProvider_ID,
        am_bm.DestCircel_ID
    from Auditdata_BillingMaster am_bm
    inner join Temp_tbl tmp
        on tmp.Provider_ID=am_bm.DestProvider_ID
        and tmp.Circel_ID=am_bm.DestCircel_ID
    where  convert(datetime,tmp.fromdate)>=
        convert(datetime,dbo.fnToDate(am_bm.BM_BillFrom),103)
    and convert(datetime,tmp.todate) <= 
        convert(datetime,dbo.fnToDate(am_bm.BM_BillTo),103)
    group by
        convert(datetime,dbo.fnToDate(BM_BillFrom),103),
        convert(datetime,dbo.fnToDate(BM_BillTo),103),
        am_bm.DestProvider_ID,
        am_bm.DestCircel_ID
    ) b
    on Temp_tbl.Provider_ID = b.DestProvider_ID
    and Temp_tbl.Circel_ID = b.DestCircel_ID
    and convert(datetime,Temp_tbl.fromdate,101)>= convert(datetime,(b.a1),101)
    and convert(datetime,Temp_tbl.todate) <= convert(datetime,(b.b1),101)

I want to merge above 2 SQL query in SQL Server 2000.

Please help me.

Thanks in advance.

Answer 1

Do you mean to JOIN or UNION both tables?

If you mean to JOIN both query results, simply take both results as input for JOIN statement.

How you join both results is really dependent on your database design. Preferably the join is based on referential integrity enforcing the relationship between the results to ensure data integrity. But since you do not mention the join condition, let me assume you will join based on DestProvider_ID & DestCircel_ID.

select 
    result1.DestProvider_ID,
    result1.DestCircel_ID,
    result1.fromdate,
    result1.todate,
    result1.BillRecevable,
    result1.Billreceied,
    result2.fromdate

from 

    ( *your first query* ) as result1

inner join

    (select
        Temp_tbl.fromdate, 
        am_bm.DestProvider_ID, 
        am_bm.DestCircel_ID

    from Temp_tbl Temp_tbl

        *the rest of your second query*

    ) as result2

    on result1.DestProvider_ID = result2.DestProvider_ID 
    and result1.DestCircel_ID = result2.DestCircel_ID

UNION:

If you want to take multiple select statements and combine them into one result set, UNION statement is the easiest way to go:

SELECT column1a, column2a, column3a FROM tableA
UNION
SELECT column1b, column2b, column3b FROM tableB

This is possible only if:

1. both queries have same number of columns.
2. Corresponding columns in each query expression must be of the same data type
   • data type of column1a == column1b
   • data type of column2a == column2b
   • data type of column3a == column3b

Since both of your quries do not have same number of columns, you can't merge them, at least with UNION select.