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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.