Help with a SQL query

I'm trying to draft a SQL Query for the following:
A bookstore that sells books - when a user selects a book to view it's
details, he should see a section that says "People who bought this book also
bought...with a list of various titles" - this is a common feature of sites
such as Barnes & Noble and Amazon.
How does the SQL Query work to get this information extracted out of the
database.
I have a Products table (with fields related to each product - Product ID,
Product Title, Product Category)
I have an Orders table (with fields related to each order - Order ID,
Persons ID, ...)
I have an Order Details table (with fields related to each item in the
order - Order ID, Product ID)RB wrote:
> I'm trying to draft a SQL Query for the following:
> A bookstore that sells books - when a user selects a book to view it's
> details, he should see a section that says "People who bought this book also
> bought...with a list of various titles" - this is a common feature of sites
> such as Barnes & Noble and Amazon.
> How does the SQL Query work to get this information extracted out of the
> database.
> I have a Products table (with fields related to each product - Product ID,
> Product Title, Product Category)
> I have an Orders table (with fields related to each order - Order ID,
> Persons ID, ...)
> I have an Order Details table (with fields related to each item in the
> order - Order ID, Product ID)
>
Assuming that you have the productID from the webpage, which will be
represented as @.PRODUCTID.
select P.productID, P.title
from Products P, OrderDetails OD
where P.productID = OD.productID and OD.PersonID IN (
select O.personID
from OrderDetails OD, Orders O
where OD.productID = @.PRODUCTID AND OD.OrderID = O.OrderID
)
Without the exact DB schema this is the best I could come up.
You will need to tweak it a little because you will get repeats etc.
It should be straight forward.

Help with a simple Query

I am trying to make a single display page for an author's books.

the books page only displays books of a type "type" (novels, non-fiction, etc)

I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this:

<asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader%>" SelectCommand="SELECT * FROM [Books] ( If @.Type <> "" then WHERE ([Type] = @.Type)) ORDER BY [SortDate] DESC">

But it doesn't seem to want to work. I get a "server tag is not well formed" error.

Try this:

SELECT*FROM [Books]WHERE (@.TypeISNULLOR [Type]=@.Type)ORDERBY [SortDate]DESC

|||

Or, for text variables:

SELECT*FROM [Books]WHERE [Type] like IsNull(@.Type,'%')ORDERBY [SortDate]DESC

That is not, by the way, an exact equivalent of the previous poster's query.

This version allows a wildcard search, whereas the other query requires an exact match.

For example, if you were wanting to search for both Novels and Novellas, a value for @.Type of 'Novel%' would get you both types of book in one query.

Numbers and dates don't work this way. :(