sql server - How to join mulitple SQL query set?

Question

I want to join different SQL result sets into one result. all the sql query should be Left join

Example:

SQL 1:

Select t1.Model_id, t1.Model_num, count(t1.Catg_code) as m_cnt
From model t1, car t2
Where t1.model_id = t2.model_id
  and t1.model = 'CD' 
Group by t1.Model_id,t1.Model_num

SQL 2:

Select t1.Model_id, t1.Model_num, count(t1.Catg_code)as e_cnt
From model t1, car t2
Where t1.model_id = t2.model_id
  and t1.model = 'EM' 
Group by t1.Model_id, t1.Model_num

SQL 3:

Select t1.Model_id, t1.prod_cd, t3.startdate, t3.enddate 
From prod t1, master t3
Where t1.model_id = t3.model_id 

How to make all above SQL result sets into one result?

This is the desired result:

Model_id  Model_num   m_cnt   e_cnt   startdate    enddate
-------------------------------------------------------------
011       100001        3       4     04/04/2020   04/04/2020
022       200001        1       2     05/05/2019   05/06/2019

Answer 1

I think the following query will handle your issue and also you should change your join style

Select t1.Model_id, t1.Model_num,NULL, count(t1.Catg_code) 
From model t1, car t2
Where t1.model_id = t2.model_id
  and t1.model = 'CD' 
Group by t1.Model_id,t1.Model_num

UNION ALL 


Select t1.Model_id, t1.Model_num,NULL, count(t1.Catg_code) ,NULL,NULL
From model t1, car t2
Where t1.model_id = t2.model_id
  and t1.model = 'EM' 
Group by t1.Model_id, t1.Model_num

UNION ALL 

Select t1.Model_id, NULL,t1.prod_cd,NULL, t3.startdate, t3.enddate 
From prod t1, master t3
Where t1.model_id = t3.model_id