How to make a job for below sql query

Seven Stars RAJ
Seven Stars

How to make a job for below sql query

Hi Talend Folks,

 

How to make a job for below sql query. I want to know job flow for this query. kindly share your information.


SELECT team, sum(bet) amt_b

FROM ab

WHERE art = 'D' AND soll = 'F'

GROUP BY team

 

except

 

SELECT team, sum(bet) amt_c

FROM abc, abcd

WHERE name = 'UBH'
AND dom = art

GROUP BY team

Thanks
RAJ
Sixteen Stars

Re: How to make a job for below sql query

You can do this in a single DB input component if you want.... so long as both tables are in the same database.

With main_flow as (
SELECT team, sum(bet) amt_b
FROM ab
WHERE art = 'D' AND soll = 'F'
GROUP BY team),

filter_flow as (
SELECT team, sum(bet) amt_c
FROM abc, abcd
WHERE name = 'UBH'
AND dom = art)

Select 
team, 
amt_b
From main_flow
Where team not in (
Select team
From filter_flow)
GROUP BY team

Otherwise you can use two DB input components and connect using a tMap. The main_flow query in the MAIN table join and the filter_flow in the other DB input connected to a lookup link in the tMap. Then join on team and select catch inner join rejects on the output of the tMap.