CODEWITHSUNDEEP
mysqlsql
user3364940
user3364940

Reputation: 144

how can i combine two mysql query to get one single result?

I have two mysql query as below

SELECT cv_requirement,cv_target,cv_target_date_for 
FROM `cv_target` 
where cv_target_date_for between '2014-02-20' and '2014-02-27' and cv_recruiter='44'

AND

select count(candidate_id)as achi,cv_target_date from candidate
where fk_posted_user_id='44'
and cv_target_date between '2014-02-20' and '2014-02-27'
group by fk_job_id,cv_target_date

the first query produce 11 record and the second one shows 10 record how i can combine this two query to get single query result to show 11 record with 10 values and 1 null value

i have tried this but showing only 10 record not null record

SELECT cv_requirement,cv_target,cv_target_date_for,count(candidate_id) achi, cv_target_date
FROM `cv_target` a
left join candidate b 
on a.cv_requirement=b.fk_job_id and a.cv_target_date_for=b.cv_target_date
where cv_target_date_for between '2014-02-20' and '2014-02-27' and cv_recruiter='44'
group by cv_requirement,cv_target

query one
    fk_job_id   achi    cv_target_date Ascending
    188         1       2014-02-20
    220         1       2014-02-20
    220         1       2014-02-21
    221         5       2014-02-21
    224         1       2014-02-22
    224         2       2014-02-24
    224         2       2014-02-25
    222         1       2014-02-25
    222         3       2014-02-26
    224         1       2014-02-27


query two

cv_requirement  cv_target   cv_target_date_for
188             2           2014-02-20
220             2           2014-02-21
221             2           2014-02-21
224             3           2014-02-22
220             1           2014-02-22
224             2           2014-02-24
222             1           2014-02-24
224             4           2014-02-25
222             4           2014-02-25
222             3           2014-02-26
224             3           2014-02-27


i want this out put

cv_requirement  cv_target   cv_target_date_for  achi
188             2           2014-02-20          1
220             2           2014-02-21          1
221             2           2014-02-21          5
224             3           2014-02-22          1
220             1           2014-02-22          0
224             2           2014-02-24          2
222             1           2014-02-24          0
224             4           2014-02-25          2
222             4           2014-02-25          1
222             3           2014-02-26          3
224             3           2014-02-27          1

pls provide some help .

Upvotes: 0

Views: 65

Answers (2)

Vikas Hardia
Vikas Hardia

Reputation: 2695

Try this 

 SELECT cv_requirement,cv_target,cv_target_date_for, achi
  FROM (SELECT cv_requirement,cv_target,cv_target_date_for 
   FROM `cv_target` 
   where cv_target_date_for between '2014-02-20' and '2014-02-27' and cv_recruiter='44') a

   left outer  join
    (
    select count(candidate_id)as achi,cv_target_date from candidate
  where fk_posted_user_id='44'
  and cv_target_date between '2014-02-20' and '2014-02-27'
  group by fk_job_id,cv_target_date) b

  on a.cv_requirement=b.fk_job_id and a.cv_target_date_for=b.cv_target_date

Upvotes: 0

Mureinik
Mureinik

Reputation: 311018

The grouping is eliminating your 11th record. You should perform the group by in an inner query, and only then join the two:

SELECT          cv_requirement, cv_target, cv_target_date_for, achi
FROM            cv_target a
LEFT OUTER JOIN (SELECT   COUNT(candidate_id) AS achi, cv_target_date 
                 FROM     candidate
                 GROUP BY fk_job_id,cv_target_date) 
ON              a.cv_requirement = b.fk_job_id AND 
                a.cv_target_date_for = b.cv_target_date
WHERE           cv_target_date_for BETWEEN '2014-02-20' AND '2014-02-27' AND 
                cv_recruiter = '44'

Upvotes: 1

Related Questions