SQLQuerryCntsSum

Does anyone know whi am not getting correct  result here

I have two tables


BOOKS
------
bkno   number(5)
bkmed  varchar2(10) (CD, DVD)


CONV
-------
bkno
bkmed


Master table is books. The book that selected to convert from CD to DVD are added to CONV
with bkmed="CD". After that a new ROW is added to BOOKS with "DVD" media.

I want to know the books in CONV that do not have a DVD record in BOOKS


sql> select count(*) from conv;
14382

sql> select bkno from conv where bkno not in (select bkno from books where bkmed='DVD')
0

sql> select bkno from conv where bkno in (select bkno from books where bkmed='DVD')
10872

so why the 3510 books which i know are not in books showing up.

Every book in CONV get added from BOOKS table.


sam15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joachim58Commented:
Have you checked data values in both tables?

SELECT  COUNT(DISTINCT bkno), bkmed FROM books
GROUP BY bkmed

SELECT  COUNT(DISTINCT bkno), bkmed FROM conv
GROUP BY bkmed


 
0
SharathData EngineerCommented:
give a try.
SELECT bkno 
  FROM conv AS c 
 WHERE NOT EXISTS (SELECT 1 
                     FROM books AS b 
                    WHERE b.bkmed = 'DVD' 
                          AND b.bkno = c.bkno)

Open in new window

0
johanntagleCommented:
Sharath's solution could work but usually SQL joins do it faster:

select c.bkno from conv c, (select * from books where bkmed='DVD') b
where c.bkno = b.bkno(+)
and b.bkno is null

Note that the above uses Oracle-proprietary outer join syntax (don't try on other databases)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam15Author Commented:
I do not understand why #3 doe not give me 3508 records too. The subset of DVD books should not have the book numbers too


1  select count(bkno) from conv C
2* where not exists (select 1 from books b where b.bkmed='DVD' and b.bkno=c.bkno)
SQL> /

COUNT(BKSEQ)
------------
        3508

1 row selected.

  1  select count(bkno) from conv C
  2* where bkno not in (select bkno from books b where b.bkmed='DVD' and b.bkno=c.bkseq)
SQL> /

COUNT(BKSEQ)
------------
        3508

1 row selected.


  1  select count(bkno) from conv C
  2* where bkno not in (select bkno from books b where b.bkmed='DVD')
SQL> /

COUNT(BKSEQ)
------------
           0

1 row selected.
0
SharathData EngineerCommented:
This query gives you zero records if bkno is NULL in books table.
select count(bkno) from conv C where bkno not in (select bkno from books b where b.bkmed='DVD')

Open in new window

This query will take care of NULLs if the sub-query returns NULL values.
SELECT bkno FROM conv AS c WHERE NOT EXISTS (SELECT 1 FROM books AS b WHERE b.bkmed = 'DVD' AND b.bkno = c.bkno)

Open in new window

'NOT IN' does not handle NULLs where 'NOT EXISTS' does.Hope you got the point.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam15Author Commented:
Yes, you are right. if i add "and bkno is not null) i get the right count.

But if NOT IN does not handle NULLS why is it finding the books number. I am not sure i fully understand it.
0
SharathData EngineerCommented:
>> But if NOT IN does not handle NULLS why is it finding the books number. I am not sure i fully understand it.
select count(bkno) from conv C where bkno not in (select bkno from books b where b.bkmed='DVD' and b.bkno=c.bkseq)

Open in new window

In this query the sub query does not return NULL value for bkno. Becuase NULLs are eliminated with the join condition "b.bkno=c.bkseq". Hence you are able to see the row count.
0
sam15Author Commented:
yes, your query is corret.

But i was trying to see techincally why SQL could not find the books in teh subquery if it was returning NULL. Does it read NULL as it if was a book number that existed.

select count(bkno) from conv C where bkno
(select bkno from books b where b.bkmed='DVD' )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.