SQL search returning duplicate values

SQL Apprentice Question
I am having some trouble. I am doing some db search tests before
moving from access to mysql/sql server. Before I get into the Full
Text Search battle.....

If I try to do the following and there are multiple stores who have the
same book, all values are returned, which make duplicate entries in the
results. How do I accomplish the following but get a single price
value? How do I get the lowest, highest, middle, n'th etc value?


SELECT DISTINCT tblBook.*, tblStore.Price
FROM tblBook INNER JOIN tblStore ON tblBook.ID = tblStore.BookID
WHERE (((tblBook.Description) Like '%value%'));


tblBook tblStore
======== ========
ID------------| ID
Value |------BookID
Description Price



Celko Answers

>> Any help would be greatly appreciated. <<


Why did you make the book price an attribute of a store instead of the
book??? Think about it.

Also, read something about ISO-11179 metadata rules, so you will stop
using things like "tblBook" (a scalar value because it is singular and
it belongs to a piece of furniture -- I would have modeled a set of
book titles in my schema). Why does the book_id attribute change names
from table to table? Did you know that the ISBN-13 is the standard
identifier for books?


In the real world, you would have several prices for a book (wholesale,
retail, discounted, etc.) and they would be with the book. The min and
max prices would be known and then you can use a weighted average to
get a "middle" value. That means you need a quantity at each price
point.


Your current design is too simple for your query. Perhaps you use a
discount scale based on volume, or classes like this:


CREATE TABLE BookSales -- plural name for a set!! no redundant prefixes
(isbn CHAR(13) NOT NULL PRIMARY KEY, -- industry standards
wholesale_price DECIMAL (8,4) NOT NULL,
wholesale_qty INTEGER DEFAULT 0 NOT NULL,
retail_price DECIMAL (8,4) NOT NULL,
retail_qty INTEGER DEFAULT 0 NOT NULL,
etc.);


Start over and get some help from someone who knows how to do the model.