Ok, I've got a problem, I've got an inventory of books they can be stored in different locations in the warehouse and I need to generate a booklist, and I want to include the title and an author, I have a table with the authors in it, and there may be anywhere from 1 to 10 authors for a particular book, but I don't really care, I just need one for displaying ... anyway, here is a layout
table stock ( qty int, isbn char(20), location int ) table books ( isbn char(20), title char(128) ) table authors ( isbn char(20), author char(120) ) table prices ( isbn char, retail decimal(7,2), wholesale decimal(7,2) ) all the tables with the exception of stock and authors have a unique index on the isbn, so only one isbn in those. Now I'm trying to generate a book list with quantities available, and it needs a short description, title, listprice, ourprice, author.. I tried this query, select sum(s.qty),s.isbn,b.title, p.listprice,p.ourprice, a.author from stock s left join books b on s.isbn=b.isbn left join prices p on s.isbn=p.isbn left join authors a on s.isbn=a.isbn group by s.isbn HAVING sum(s.qty) > 10; now this works ok, but of course if I have more than one author the actual sum of the books is multiplied by the actual amount of authors listed for the book, now I don't really care what author shows up, it's just mostly a filler for the list, but I finally struck on this solution and now I just need to know if it should be reliable...? select sum(s.qty) ,s.isbn,p.listprice,p.ourprice, a.author,count(distinct a.author) from stock s left join books b on s.isbn=b.isbn left join prices p on s.isbn=p.isbn left join authors a on s.isbn=a.isbn group by s.isbn HAVING sum(s.qty) > 10; There is a possiblity, that there may not be an author listed for a particular isbn, or I would do the math in the query, but using the above, I should be able to test the count of distinct a.author and divide sum.qty by the distinct a.authors and come up with a legit count for the actual count of books I have, of course testing for zero count in the distinct a.author to avoid diving by zero, and using this I believe that I should have the correct count in inventory? Should this work and be reliable? Long winded, but just wanting to make sure I'm understood.. Kelley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
