Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cobain4pr
Starting Member
1 Post |
 Posted - 2007-03-02 : 17:21:12
|
| I need to write an sql statement that finds all names of people that have borrowed all books of a certain publisher. the tables are as follows:employee(empno string, name string, salary double)book(isbn string, title string, publisher string)loan(empno string, isbn string , date Date)the query i came up with is :Select e.name from employee e where not exists(SELECT isbn FROM book where publisher='Waterhouse' and isbn not in(Select isbn from loan l where l.empno = e.empno));I have three names in my table but only one of them borrowed all books of the a certain publisher. but the above query gives me all people that ever borrowed a book. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-02 : 17:53:28
|
| select y.empnofrom (select publisher, count(*) as cnt from book group by publisher) as xinner join (select b.publisher, l.empno, count(*) as cnt from loan as l inner join book as b on b.isbn = l.isbn group by b.publisher, l.empno) as y on y.publisher = x.publisherwhere x.cnt = y.cntPeter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
SQL Query Problem