CODEWITHSUNDEEP
sqljointeradata
u356877
u356877

Reputation: 9

joining on a query result

i'm new to sql and having trouble joining a query result table with an existing table. i've been trying to name the query result as res_tab but it doesn't seem to work.i just want to be able to join the query result with an existing table. here's what i have so far:

(select distinct op_id
from cmpr_dept_vmdb.cust_promotion
where promo_id in ('TB4M40', 'TB4M41', 'TB4M42')
and regstrn_status_cd = 'R') as res_tab;

select elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
where elite_hist.instant_elt_promo_cd in ('F1', 'F2', 'F3')
inner join elite_hist
on res_tab.op_id = elite_hist.op_id

it's returning the following error: Syntax error: expected something between ')' and the 'as' keyword

Upvotes: 0

Views: 62

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems to want subquery with correlation approach correctly 

select distinct elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
where instant_elt_promo_cd in ('F1', 'F2', 'F3') and 
      exists (select 1 
              from cmpr_dept_vmdb.cust_promotion as res_tab
              where res_tab.op_id = elite_hist.op_id and
                    res_tab.instant_elt_promo_cd in ('F1', 'F2', 'F3') and
                    res_tab.regstrn_status_cd = 'R
             );

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

SQL select syntax is 

[SELECT] ...
[FROM] .....
[JOIN] ....
[WHERE] ....
[GROUP BY] .....

You seem like want to join like this.

select elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
inner join 
(
    select distinct op_id
    from cmpr_dept_vmdb.cust_promotion
    where promo_id in ('TB4M40', 'TB4M41', 'TB4M42')
    and regstrn_status_cd = 'R'
) as res_tab;
on res_tab.op_id = elite_hist.op_id
where elite_hist.instant_elt_promo_cd in ('F1', 'F2', 'F3')

Upvotes: 1

Related Questions