Link to home
Start Free TrialLog in
Avatar of sam15
sam15

asked on

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.


Avatar of joachim58
joachim58
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


 
Avatar of Sharath S
Sharath S
Flag of United States of America image
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

Avatar of johanntagle
johanntagle
Flag of Philippines image
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)
Avatar of sam15
sam15

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam15
sam15

ASKER

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.
Avatar of Sharath S
Sharath S
Flag of United States of America image
>> 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.
Avatar of sam15
sam15

ASKER

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' )