Create SQL Queries!

[Arpan]

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.
Not only this, the options in the different drop-down lists EXCEPT for
the author drop-down list should also change & contain only those
records that pertain to Author1. Like for e.g. only 2 publishers namely
Publisher1 & Publisher4 have published the books written by Author1.
Under such circumstances, the publisher drop-down list should have only
2 options - Publisher1 & Publisher4. Another e.g. is that the books
written by Author1 either belong to Category2 or Category5. So the
category drop-down should have only Category2 & Category5 as the
options when Author1 is selected.

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.

Another very important point is let whatever records be selected by the
user from any of the drop-down lists, all the drop-down lists should
change accordingly but NOT the author drop-down list. The authors
drop-down list should ALWAYS contain ALL the records that are present
under the column named 'Author' in the DB table.

Another area where I am getting stuck is suppose after selecting
Author1 from the author drop-down list & Publisher1 from the publisher
drop-down list, the user selects, say, Author3 from the author
drop-down list. Under such circumstances, the earlier 2 selections made
(Author1 & Publisher1) should no longer play any part & the user should
be displayed only those records (or books) that Author3 has written.
Subsequent selections in the different drop-down lists shouldn't
involve Author1 & Publisher1 in any way (of course unless the user
selects either Author1 & Publisher1 or both again).

How do I accomplish these tasks?

Thanks,

Arpan

 

[Ray Costanzo [MVP]]

http://www.aspfaq.com/show.asp?id=2270

Ray at work

 

[Arpan]

Thanks Ray for your help. I had a look at the different articles but to
be honest, all of them address only the secondary part of my needs.

More than that, I need a solution to create the SQL queries dynamically
as & when a user selects an option from any of the multiple select
lists especially when, after an option is selected from one drop-down,
the user selects another option from another drop-down so that the SQL
query includes both the conditions using the 'AND' operator.

Thanks once again,

Regards,

Arpan