MySQL Forums
Forum List  »  General

rewriting subqueries
Posted by: David Heald
Date: February 21, 2005 09:19PM

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

Options: ReplyQuote


Subject
Written By
Posted
rewriting subqueries
February 21, 2005 09:19PM
March 23, 2005 02:57AM


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.