CODEWITHSUNDEEP
sqlsql-server
Sadia
Sadia

Reputation: 181

Adding data into one table from two select queries targeting different number of columns from two different tables

I have two select queries having different number of columns in each query with different where conditions.I want to add data gathered from two select queries into one table. i have seen method of doing "UNION" but in that case columns in two queries need to be same in number how can i add data from two queries into single table. My queries are as under:

Select 
Village_ID,
Village_Name,
RSP_ID,
Other_Loan_Source,
Informal_Money_Lender_Loans
from(
Select DISTINCT SETTLEMENT_ID as Village_ID,
SETTLEMENT_NAME as Village_Name,
RSP_ID,
(SELECT COUNT(*) FROM PscData where SETTLEMENT_ID  = T.SETTLEMENT_ID 
AND TAKEN_LOAN = 6 )as Other_Loan_Source,
(SELECT COUNT(*) FROM PscData where SETTLEMENT_ID  = T.SETTLEMENT_ID 
AND TAKEN_LOAN = 5 )as Informal_Money_Lender_Loans

FROM PscData as T

)tmp

and the second query is this:

SELECT
        SUM(CASE WHEN  t2.SEX = 1
        THEN 1 ELSE 0 END) AS Total_Males,

        SUM(CASE WHEN  t2.SEX = 2
        THEN 1 ELSE 0 END) AS Total_Females,

        SUM(CASE WHEN   t2.MARITAL_STATUS = 1
        THEN 1 ELSE 0 END) AS Total_Married,
        t1.SETTLEMENT_ID 
FROM PScData t1 
INNER JOIN PscMemberData t2
    ON t2._PARENT_AURI = t1.URI
GROUP BY
    t1.SETTLEMENT_ID
HAVING COUNT(*) > 1;

how do i combine results of these into one table.

Upvotes: 0

Views: 54

Answers (2)

Ray Krungkaew
Ray Krungkaew

Reputation: 6973

Why don't you just add a dummy column to the second table?

SELECT
        SUM(CASE WHEN  t2.SEX = 1
        THEN 1 ELSE 0 END) AS Total_Males,

        SUM(CASE WHEN  t2.SEX = 2
        THEN 1 ELSE 0 END) AS Total_Females,

        SUM(CASE WHEN   t2.MARITAL_STATUS = 1
        THEN 1 ELSE 0 END) AS Total_Married,
        t1.SETTLEMENT_ID ,
        '' Informal_Money_Lender_Loans
FROM PScData t1

Upvotes: 0

Steef
Steef

Reputation: 333

You can first create a table using the union and only the fields that exist in both tables. Then you join this table to the original tables to add the remaining fields.

with cte as (
select Village_ID as village_settlement_id
  from #tmp_1
 union 
select SETTLEMENT_ID as village_settlement_id
  from #tmp_2
)
select * 
     , #tmp_1.Village_ID
     , #tmp_1.Village_Name
     , #tmp_1.RSP_ID
     , #tmp_1.Other_Loan_Source
     , #tmp_1.Informal_Money_Lender_Loans
     , #tmp_2.Total_Males
     , #tmp_2.Total_Females
     , #tmp_2.Total_Married
  from cte 
  left join #tmp_1 on cte.village_settlement_id = #tmp_1.Village_ID  
  left join #tmp_2 on cte.village_settlement_id = #tmp_1.SETTLEMENT_ID

Upvotes: 0

Related Questions