multiple combination searching query
-
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me? Thanks in advance.
-
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me? Thanks in advance.
dream_liner_7e7 wrote:
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
What have you tried so far? If you can post the query that you now have, and also tell us where you are stuck, people here will be more than willing to try and help you.
-
dream_liner_7e7 wrote:
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
What have you tried so far? If you can post the query that you now have, and also tell us where you are stuck, people here will be more than willing to try and help you.
SELECT [AIMS_Library.Reck].Reck_Code + ' ' + [AIMS_Library.Reck].Shelf_Code AS [Reck & Shelf], [AIMS_Library.Category].Category_Name, [AIMS_Library.BookDetails].Qty, [AIMS_Library.BookDetails].ISBN, [AIMS_Library.BookDetails].BookTitle, [AIMS_Library.BookDetails].Author, [AIMS_Library.BookDetails].Publisher, [AIMS_Library.BookDetails].Edition, [AIMS_Library.BookDetails].YearPublished FROM [AIMS_Library.BookDetails], [AIMS_Library.Category], [AIMS_Library.Reck] where [AIMS_Library.Reck].reck_id = [AIMS_Library.BookDetails].reck_id and [AIMS_Library.Category].category_id = [AIMS_Library.BookDetails].category_id and [AIMS_Library.BookDetails].author = 'abc' or [AIMS_Library.BookDetails].publisher = 'xyz' the last line thats where i stuck, when from my c# form users will try to search books. Is there any 1 query solution or i have to write multiple queries? Thanks in advance.
-
SELECT [AIMS_Library.Reck].Reck_Code + ' ' + [AIMS_Library.Reck].Shelf_Code AS [Reck & Shelf], [AIMS_Library.Category].Category_Name, [AIMS_Library.BookDetails].Qty, [AIMS_Library.BookDetails].ISBN, [AIMS_Library.BookDetails].BookTitle, [AIMS_Library.BookDetails].Author, [AIMS_Library.BookDetails].Publisher, [AIMS_Library.BookDetails].Edition, [AIMS_Library.BookDetails].YearPublished FROM [AIMS_Library.BookDetails], [AIMS_Library.Category], [AIMS_Library.Reck] where [AIMS_Library.Reck].reck_id = [AIMS_Library.BookDetails].reck_id and [AIMS_Library.Category].category_id = [AIMS_Library.BookDetails].category_id and [AIMS_Library.BookDetails].author = 'abc' or [AIMS_Library.BookDetails].publisher = 'xyz' the last line thats where i stuck, when from my c# form users will try to search books. Is there any 1 query solution or i have to write multiple queries? Thanks in advance.
One way to solve this would be to dynamicallyl create the query based on user selection... keep the basic query (including fields needed and joins) a constant, and add to the query string for each selection made. So, if user has selected author 'xyz' and publisher 'abc' qry = [basic query here, including the joins] if [author selected] then qry = constant + " and author=" + selected author if [publisher selected] then qry = constant + " and publisher=" + selected publisher ... and so on You might want to look into creating parameters for your SQL query, to prevent injection attacks. HTH
-
SELECT [AIMS_Library.Reck].Reck_Code + ' ' + [AIMS_Library.Reck].Shelf_Code AS [Reck & Shelf], [AIMS_Library.Category].Category_Name, [AIMS_Library.BookDetails].Qty, [AIMS_Library.BookDetails].ISBN, [AIMS_Library.BookDetails].BookTitle, [AIMS_Library.BookDetails].Author, [AIMS_Library.BookDetails].Publisher, [AIMS_Library.BookDetails].Edition, [AIMS_Library.BookDetails].YearPublished FROM [AIMS_Library.BookDetails], [AIMS_Library.Category], [AIMS_Library.Reck] where [AIMS_Library.Reck].reck_id = [AIMS_Library.BookDetails].reck_id and [AIMS_Library.Category].category_id = [AIMS_Library.BookDetails].category_id and [AIMS_Library.BookDetails].author = 'abc' or [AIMS_Library.BookDetails].publisher = 'xyz' the last line thats where i stuck, when from my c# form users will try to search books. Is there any 1 query solution or i have to write multiple queries? Thanks in advance.
What ChandraRam suggested is a good way to go. However, also notice that you may have a potential problem in your query. Since you have an
ORin the middle of the conditions, but no parenthesis used the result may be unexpected and in your example lead to cartesian product.The need to optimize rises from a bad design
-
One way to solve this would be to dynamicallyl create the query based on user selection... keep the basic query (including fields needed and joins) a constant, and add to the query string for each selection made. So, if user has selected author 'xyz' and publisher 'abc' qry = [basic query here, including the joins] if [author selected] then qry = constant + " and author=" + selected author if [publisher selected] then qry = constant + " and publisher=" + selected publisher ... and so on You might want to look into creating parameters for your SQL query, to prevent injection attacks. HTH
thank you every1 thats a valuable solution