How to combine different queries results into a single query result table

Question

Here is a thing.I am trying to find a query which can include all there 3 results. But I I only know how to do the query for one of them each.

Questions: For each survey that has had at least 200 members participate, provide the following information: 1）Survey ID and Survey Description 2）Number of members that started the survey 3) Number of members that already finished.

query for survey ID and survey description which have at least 200 participations

1)

Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
from  Tbl_survey,Tbl_member_participation
where Tbl-survey.member_id = Tbl_member_participation.member_id
group by survey_id, Survey_desc
having totalnumber >= 200

2) query for number of members that started but not finished.

select count(survey_id)
from  Tbl_survey, 
where survey_id exists ( Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
                       from  Tbl_survey,Tbl_member_participation
                       where Tbl-survey.member_id = Tbl_member_participation.member_id
                       group by survey_id, Survey_desc
                       having totalnumber >= 200) and
     finishi_date is **null**   
group by survey_id

3) query for number of members that already finished.

select count(survey_id)
from  Tbl_survey, 
where survey_id exists ( Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
                       from  Tbl_survey,Tbl_member_participation
                       where Tbl-survey.member_id = Tbl_member_participation.member_id
                       group by survey_id, Survey_desc
                       having totalnumber >= 200) and
     finishi_date is ***not null***   
group by survey_id

SO I just want a way can combine these three results like Survey Id, Survey desc, start people, finish people. in a only one query table.

Answer 1

First of all, you should get your queries right.

1) Your first query is okay, but you should not use the out-dated comma-separated join syntax. Use explicit joins instead (that where introduced in SQL standard some twenty years ago, btw).

select s.survey_id, s.survey_desc, count(*) as totalnumber
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id, s.survey_desc
having count(*) >= 200;

2,3) Your second and third queries are syntactically broken. You are kind of mixing EXISTS and IN. Moreover finishi_date is likely to be a member of tbl_member_participation, so you must join this table again. Here is query 2 corrected:

select s.survey_id, count(*)
from  tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
where survey_id in
(
  select s.survey_id
  from tbl_survey s
  join tbl_member_participation mp on s.member_id = mp.member_id
  group by s.survey_id
  having count(*) >= 200
)
and mp.finishi_date is null
group by s.survey_id;

In order to combine all three, you don't have to use EXISTS or IN. All the data needed is available in query 1 already. Look, how I modify query 1 to get to a much simpler query 2:

select 
  s.survey_id, 
  sum(case when mp.finishi_date is null then 1 else 0 end) as count_unfinished
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id
having count(*) >= 200;

Having said this, your final query is this:

select 
  s.survey_id, 
  s.survey_desc, 
  sum(case when mp.finishi_date is null then 1 else 0 end) as count_unfinished,
  sum(case when mp.finishi_date is not null then 1 else 0 end) as count_finished,
  count(*) as totalnumber
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id, s.survey_desc
having count(*) >= 200;