CODEWITHSUNDEEP
sqloracle-database
user7403515
user7403515

Reputation:

Using Not in in SQL query

I have two tables billcrown and bank_details. In billcrown there exist a unique row for each PJNO:

PJNO GRIRNO AMT
---- ------ ----
PJ1   GRIR1 1000
PJ2   GRIR2 150

Table bankdetails has 

PJNO GRIRNO AMT
---- ------ ---
PJ1  GRIR1  100
PJ2  GRIR2  150 
PJ1  GRIR1  200

I want to display all PJNO of the table billcrown where the sum of AMT of this PJNO in bankdetails is not equal to the AMT of this PJNO in billcrown

Here PJ1 in billcrown's amount is 1000 but the sum of amounts in bankdetails for the PJ1 is 300. So it should be displayed. But in the case of PJ2 the billcrown and bankdetails amount is the same.

Upvotes: 1

Views: 113

Answers (5)

Littlefoot
Littlefoot

Reputation: 142713

Something like this, perhaps?

select c.pjno
from billcrowm c join bankdetails d on d.pjno = c.pjno
group by c.pjno
having sum(c.amt) <> sum(d.amt);

[EDIT, after new information has been revealed]

Outer join with NVL might do the job.

select c.pjno
from billcrowm c left join bankdetails d on c.pjno = d.pjno
group by c.pjno
having sum(c.amt) <> nvl(sum(d.amt), 0);

[EDIT #2, after reading Sharad's comment]

with csum as 
  (select pjno, grirno, sum(amt) amt
   from billcrowm
   group by pjno, grirno
  ),
dsum as
  (select pjno, grirno, sum(amt) amt
   from bankdetails
   group by pjno, grirno
  )
select c.pjno
from csum c left join dsum d on c.pjno = d.pjno
group by c.pjno
having sum(c.amt) <> sum(nvl(d.amt, 0));

Upvotes: 1

Anju Maharjan
Anju Maharjan

Reputation: 51

SELECT b.PJNO FROM billcrowm b
LEFT JOIN 
(
    SELECT PJNO, sum(amt) AS total_amt FROM bankdetails GROUP BY PJNO
)d
ON b.PJNO = d.PJNO
WHERE COALESCE(b.amt,0) <> COALESCE(d.total_amt,0);

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

If I understand well, all you need is a GROUP BY with an OUTER JOIN.

With these tables:

create table billcrown (PJNO, GRIRNO, AMT) as (
select 'PJ1', 'GRIR1', 1000 from dual union
select 'PJ2', 'GRIR2',  150 from dual union
select 'PJXX','XXXXX',  100 from dual
);
create table bankdetails (PJNO, GRIRNO, AMT) as (
select 'PJ1',  'GRIR1',  100 from dual union 
select 'PJ2',  'GRIR2',  150 from dual union  
select 'PJ1',  'GRIR1',  200 from dual
);

this

select bc.PJNO, bc.GRIRNO, bc.AMT, sum(bd.amt) 
from billCrown bc
        left join bankDetails bd
        on (bd.PJNO = bc.PJNO
            and bd.GRIRNO = bc.GRIRNO -- do you need this?
           )
group by bc.PJNO, bc.GRIRNO, bc.AMT
having sum(nvl(bd.amt, 0)) != bc.amt

gives:

PJNO GRIRNO        AMT SUM(BD.AMT)
---- ------ ---------- -----------
PJXX XXXXX         100            
PJ1  GRIR1        1000         300

Upvotes: 0

Ajay Gupta
Ajay Gupta

Reputation: 1845

This would work. 

with BankDetailsCTE 
as
(
Select PJNO, sum(AMT) as amt from BankDetails group by PJNO
)
Select d.Pjno from Billcrown d 
left join BankDetailsCTE c
on d.pjno = c.pjno
where d.AMT <> c.amt or c.amt is null

Upvotes: 0

Diptee Hamdapurkar
Diptee Hamdapurkar

Reputation: 391

IF OBJECT_ID('tempdb..#myTemp') IS NOT NULL
  DROP TABLE #myTemp

SELECT * INTO #myTemp FROM 
 (SELECT PJNO, SUM(AMT) AS Amount FROM 
 bankdetails 
 GROUP BY PJNO)
 AS tempResult

SELECT T.[PJNO], T.Amount, (T1.AMT - T.Amount) As AmountDiff
FROM #myTemp T
INNER JOIN  billcrowm  T1 ON T.[PJNO] = T1.[PJNO]
WHERE T.Amount <> T1.AMT

Upvotes: 0

Related Questions