very confusing query !

[tom22london]

how can i find title of book whcih have the same publisher 'tom', but i am not supposed to include 'tom' in my output.

following are the tables,

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)

--------------------------------------------

 

[JamesLean]

Is 'tom' a publisher name? If so:

SELECT bt_name
FROM book_title b JOIN publisher p ON b.pub_id = p.pub_id
WHERE pub_name = 'tom'

--James

 

[tom22london]

i have solved that query by myself so this was the answer,

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='tom')) and bt_name <> 'tom';

 

[tlbroadbent]

Tom,

You may have solved the problem but in an inefficient way. James' query is much more efficient. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[tom22london]

James' query is much more efficient but the thing is that error is coming in it. so if u can answer the query in an efficient manner than i will be thankful to u.

 

[JamesLean]

It looks like I was wrong in my assumption that 'tom' was a publisher - it is actually the name of a book and you want all other books that had the same publisher? This query should do that:

SELECT bt_name
FROM book_title bt1
WHERE pub_id = (
  SELECT pub_id
  FROM book_title
  WHERE bt_name = 'tom'
  )
  AND bt_name <> 'tom'

--James