rewriting subqueries
I'm moving some SQL to a 4.0.18 server and I have to change the SQL to get rid of subqueries. Here is a fake example that illustrates my problem:
mysql> select * from owner;
| user_id | name |
| 1 | Dave |
| 2 | Joe |
| 3 | Mary |
mysql> select * from booklist;
| book_id | name | price |
| 23 | Lord of the Flying Rings | 3 |
| 24 | Mime for Clowns | 3 |
| 25 | SQL for Twits | 5 |
| 26 | Advanced SQL for twits | 4 |
mysql> select * from booksowned;
| user_id | book_id |
| 1 | 24 |
| 1 | 25 |
| 2 | 23 |
| 2 | 24 |
| 2 | 25 |
| 3 | 23 |
// Get all the books priced < 5 that Dave doesn't own.
mysql> select * from booklist where price < 5 and book_id NOT IN (
-> select book_id from booksowned where user_id = 1);
| book_id | name | price |
| 23 | Lord of the Flying Rings | 3 |
| 26 | Advanced SQL for twits | 4 |
Can I write the above query, without the NOT IN clause? Thanks,
Dave
Subject
Written By
Posted
rewriting subqueries
February 21, 2005 09:19PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.