Wierd Query

[ronnie]

I have 2 tables with the following data

Authors table
----------------
name city
------------------------------ ------------------------------
a1 stamford
a2 rego park
a3 los angeles
a4 dallas


Publishers Table
-------------------
name city
------------------------------ ------------------------------
p1 norwalk
p2 rego park
p3 santa monica
p4 dallas


I want to write a query which finds the authors who live in a city where no publishers are located and the correct answer would be

name city
------------------------------ ------------------------------
a1 stamford
a3 los angeles

However when i write queries like this it gives me


select distinct substr(authors.name,1,30)"name",substr(authors.city,1,30)"city"
from authors,publishers where authors.city <> publishers.city
/


name city
------------------------------ ------------------------------
a1 stamford
a2 los angeles
a3 dallas
a4 rego park




select distinct substr(authors.name,1,30)"name",substr(authors.city,1,30)"city"
from authors,publishers where authors.city not in (select city from publishers where authors.city <> publishers.city)
/

name city
------------------------------ ------------------------------
a1 stamford
a2 los angeles
a3 dallas
a4 rego park


My question is why. Shouldnt both the queries work. I am puzzled as to why are they not working.

But this query works for obvious reasons

select * from authors where city not in (select distinct city from
publishers)




name city
------------------------------ ------------------------------
a1 stamford
a3 los angeles


Please Help me solve this

Ronnie

[pando]

try

select distinct substr(a.name,1,30)"name",substr(a.city,1,30)"city" 
from authors a
where a.city not in (select city from publishers p where a.city = p.city) 
/

[Edited by pando on 10-23-2001 at 02:28 PM]

[ronnie]

Hi pando,

This is what I get when I execute your query which is not the result set i am looking for

SQL> select distinct substr(a.name,1,30)"name",substr(a.city,1,30)"city"
2 from authors a
3 where a.city not in (select city from publishers p where a.city <> p.city)
4 /

name city
------------------------------ ------------------------------
a1 stamford
a2 rego park
a3 los angeles
a4 dallas


Thanks
Ronnie

[tamilselvan]

The right query is:

select author.name , author.city
from author
where not exists (select 'x' from publ where publ.city = author.city )

[ronnie]

Originally posted by tamilselvan
The right query is:

select author.name , author.city
from author
where not exists (select 'x' from publ where publ.city = author.city )

Thanks Tamilselvan

Your query works and so does

select * from authors where city not in (select distinct city from
publishers)


but my question is why do the other 2 queries in my first post do not work. Any ideas

Thanks
Ronnie

[pando]

because your query is not correlated subquery

[ronnie]

Originally posted by pando
because your query is not correlated subquery

Hi ,

Sorry for my lack of knowledge here but what do you mean by not a correlated sub query

Ronnie

[pando]

CORRELATED SUBQUERIES

STEPS PERFORMED BY THE CORRELATED SUBQUERY:

Select a Row from the Outer Query

Determine the value of the correlated column(s)

For each record of the outer query, the inner query is executed

The result of the inner query is then fed to the outer query and evaluated. If it satisfies the criteria, the row is returned for output

The next record of the outer query is selected and steps 2 through 4 are repeated until all the records of the outer query are evaluated

[tamilselvan]

Take the first query:
select distinct substr(authors.name,1,30)"name",substr(authors.city,1,30)"city"
from authors,publishers where authors.city <> publishers.city
/

First Oracle makes a cartesian product of authors and publicshers tables that produce 16 rows. Then second eliminate rows where ctities are not equal. That will produce 4 rows. Third, it sorts and eliminates duplicate rows.
That is why you get wrong results.

Your 2nd Query
select distinct substr(authors.name,1,30)"name",substr(authors.city,1,30)"city"
from authors,publishers where authors.city not in (select city from publishers where authors.city <> publishers.city)
/
First a cartesian product is done with authors and publishers tables, that produce 16 rows. The subquery is executed for row in the outer query, that would result 3 rows out of possible 4 rows in publishers table.
14 rows in the outer query satisifies WHERE clause.
Finally out of 14, distinct values are selected,that is 4 rows.

[ronnie]

Thanks a lot guys. It was Soooooooo releiving to sort this out.

I am closing the thread now.

Ronnie