is there any other shorter way to write these queries

[tom22london]

following are the three queries but its too long however answer is alright, but is there any way to make these query shorter

Following are the tables (Feilds with star are primary keys)

Borrower(bor_id*, bor_name, bor_maxbooks)

Book_title(bt_id*, bt_name,pub_id, value)

Book_copy(bc_id*, bt_id, date acquired)

Loan(bor_id, bc_id, date_out, date_due, date_back)

Publisher(pub_id*, pub_name, pub_address)

Author(author_id*, author_name)

Authorship(author_id, bt_id)

1.
select bor_name from borrower
where bor_id in
(select bor_id from loan
where bc_id in
(select bc_id from book_copy
where bt_id in
(select bt_id from book_title
where bt_id in
(select bt_id from authorship
where authorship.author_id in
(select author_id from author
where author.author_name = 'Roald Dahl')))))

2.select bor_name from borrower
where bor_id not in
(select bor_id from loan
where bc_id not in
(select bc_id from book_copy
where bt_id not in
(select bt_id from book_title
where bt_id not in
(select bt_id from authorship
where authorship.author_id not in
(select author_id from author
where author.author_name = 'Roald Dahl')))))

3. select bt_name from book_title bt1
where pub_id in
(select pub_id from publisher
where pub_id in
(select pub_id from book_title bt2
where bt2.bt_name='The Twits')) and bt_name <> 'The Twits'

 

[JamesLean]

I suggest you start using JOINs rather than all those IN statements, eg your first query could be re-written like:

SELECT bor_name
FROM borrower b JOIN loan l ON b.bor_id = l.bor_id
  JOIN book_copy bc ON l.bc_id = bc.bc_id
  JOIN book_title bt ON bc.bt_id = bt.bt_id
  JOIN authorship a ON bt_id = a.bt_id
  JOIN author au ON a.author_id = au.author_id
WHERE au.author_name = 'roald dahl'

--James

 

[JamesLean]

BTW, I've also answered your third query in a previous thread: thread220-497281 --James

 

[expostfacto]

it really depends on your database's planner whether the join will produce faster results or not. Stylistically, it's better to only join to tables you are actually selecting data from.

If using a subselect, however, you should use exists rather than in:

... where exists (select 1 from loan where loan.bor_id = borrowoer.bor_id) ...

this allows it to stop executing the subselect as soon as it finds a match.

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[swampBoogie]

>> this allows it to stop executing the subselect as soon as it finds a match.>>

So it can with in. Maybe you are thinking of the semantic difference between

= (subselect)

in (subselect)

 

[expostfacto]

perhaps a sufficiently smart planner will be able to see that the IN is functionally equivalent to the exists, but I have not seen any vendor's database do this correctly.

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[swampBoogie]

I know at least one DBMS that transforms an in predicate to an exists predicate. The transformation is straightforward and easily implemented.

 

[expostfacto]

out of curiosity, which is that?

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[swampBoogie]

It's called Mimer. There is a forum dedicated to mimer here at tek tips.

More info at

http://www.mimer.com