CODEWITHSUNDEEP
oracle-databasesubquery
user747291
user747291

Reputation: 821

Oracle subquery suggestion

I have the below 2 queries. I am having a tough time to incorporate the 2nd query into the first; both the queries can be joined by jobid. Basically what I am looking to do is incorporate the 2 total columns from the 2nd query to the first one by joining with the jobid, ie total_opens and unique_open_cnt. I hope this is clear. Any ideas are really appreciated.Thanks in advance.

SELECT u.fname, u.lname, j.job_id, j.title, ja.SENT_DATE, 
count(distinct ja.email_id) as total_sent,
COUNT(je.EMAIL_ID) as total_clicks
            FROM jobs_table j , user_table u ,
             job_alerts ja
            left join job_eml_track je
            on ja.EMAIL_ID = je.EMAIL_ID
            WHERE j.user_id = u.user_id 
            and ja.JOB_ID = j.JOB_ID
            and ja.JOB_ID = 116 
            group by j.job_id, j.title,ja.SENT_DATE,j.EMPLOYER_ID,u.fname, u.lname    

SELECT COUNT(*) AS total_opens , 
   COUNT(DISTINCT userd) AS unique_open_cnt,
   REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i') as jobid
 FROM  basetable v
WHERE v.id LIKE 'testnew%'
and REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i') = 116
group by REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i')

Upvotes: 0

Views: 56

Answers (2)

kevinskio
kevinskio

Reputation: 4551

You should be able to use the WITH clause. I have not checked the syntax but something like

with a AS
(SELECT u.fname, u.lname, j.job_id, j.title, ja.SENT_DATE, 
count(distinct ja.email_id) as total_sent,
COUNT(je.EMAIL_ID) as total_clicks
            FROM jobs_table j , user_table u ,
             job_alerts ja
            left join job_eml_track je
            on ja.EMAIL_ID = je.EMAIL_ID
            WHERE j.user_id = u.user_id 
            and ja.JOB_ID = j.JOB_ID
            and ja.JOB_ID = 116 
            group by j.job_id, j.title,ja.SENT_DATE,j.EMPLOYER_ID,u.fname, u.lname  ),
b as
(
SELECT COUNT(*) AS total_opens , 
COUNT(DISTINCT userd) AS unique_open_cnt,REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i') as jobid
 FROM  basetable v
WHERE v.id LIKE 'testnew%'
and REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i') = 116
group by REGEXP_REPLACE(v.id, '^testnew_(\d+)', '\1', 1, 1, 'i'))
select a.fname, a.lname -- and so on....
from a, b
where a.jobid = b.jobid
order by a.jobid

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

That might be something like

select * from 
(<your first query>) q1
inner join
(<your second query>) q2
  on q1.job_id = q2.jobid

Upvotes: 1

Related Questions