Dynamic SQL Queries!

An ASP application retrieves the DISTINCT records from all the columns
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--