| Groups | Blog | Home |
inetserver asp db : Help with query
now: SELECT distinct Category_Ipk AS category_id, category_description, classification_name, classification_ipk FROM view_product WHERE (Product_Search LIKE '%dog%' OR product_search LIKE '%dog%' OR category_description LIKE '%dog%') AND City_id IN (16065) AND display_from <= '6/22/2006' and display_to>= '6/22/2006' UNION SELECT DISTINCT Category_Ipk as category_id, category_description, classification_name, classification_ipk FROM view_vendor_results_CA WHERE ( Vendor_Search LIKE '%dog%') AND City_id IN (16065) ORDER BY classification_ipk This query gives a list of categories in a certain city where there is a product or vendor whose name/description is like DOG. I am using another query somewhere else that is really fast and its using a LEFT OUTER JOIN so it gives me count of products/vendors. If the product count is 0, then I do one thing, if its more, then I do something else. This is much more efficient, because I am not having to go through a middle man. Here is my other query: SELECT vr.category_description AS description, vr.category_ipk AS cid, vr.classification_ipk AS clid, count(product_ipk) AS products, count(vr.vendor_ipk) AS vendors FROM view_vendor_results_CA vr WITH(NOEXPAND) LEFT OUTER JOIN view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk WHERE (vr.city_id = 18 AND vr.classification_ipk = 1) GROUP BY vr.category_description, vr.category_ipk, vr.classification_ipk ORDER BY vr.classification_ipk, vr.category_description I must be doing something wrong, because when I try to modify this to include search parameters, it is showing no records. I know in fact, that there are two records with the UNION query. Any help with this would be greatly appreciated. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
[quoted text, click to view]
Adrienne Boswell wrote: > I am using MS SQL Server/ASP Classic and this is the query I am using > now: > > SELECT distinct Category_Ipk AS category_id, category_description, > classification_name, classification_ipk > FROM view_product > WHERE > (Product_Search LIKE '%dog%' OR product_search LIKE '%dog%' What is the point of this? You're searching the same column twice ... ? [quoted text, click to view] > OR category_description LIKE '%dog%') > AND City_id IN (16065) > AND display_from <= '6/22/2006' and display_to>= '6/22/2006' > UNION > SELECT DISTINCT Category_Ipk as category_id, category_description, > classification_name, classification_ipk > FROM view_vendor_results_CA > WHERE ( Vendor_Search LIKE '%dog%') > AND City_id IN (16065) > ORDER BY classification_ipk > > This query gives a list of categories in a certain city where there is > a product or vendor whose name/description is like DOG. > > I am using another query somewhere else that is really fast and its > using a LEFT OUTER JOIN so it gives me count of products/vendors. If > the product count is 0, then I do one thing, if its more, then I do > something else. This is much more efficient, because I am not having > to go through a middle man. Much more efficient than what? What do you mean by "middle man"? This looks like a situation tailor-made for a stored procedure, depending on the "things" you are doing ... [quoted text, click to view] > > Here is my other query: I'm not clear ... is this a third query? or is it the query you described in the previous paragraph? [quoted text, click to view] > SELECT vr.category_description AS description, vr.category_ipk AS cid, > vr.classification_ipk AS clid, count(product_ipk) AS products, > count(vr.vendor_ipk) AS vendors > FROM view_vendor_results_CA vr WITH(NOEXPAND) Oh, so this is an indexed view ...? [quoted text, click to view] > LEFT OUTER JOIN Hard to say without seeing your attempts to add search parameters. Are you > view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk > WHERE (vr.city_id = 18 AND vr.classification_ipk = 1) > GROUP BY vr.category_description, vr.category_ipk, > vr.classification_ipk > ORDER BY vr.classification_ipk, vr.category_description > > I must be doing something wrong, because when I try to modify this to > include search parameters, it is showing no records. I know in fact, > that there are two records with the UNION query. > turning it into a stored procedure? -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
Gazing into my crystal ball I observed "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> writing in news:essjiFmlGHA.5036@TK2MSFTNGP05.phx.gbl: [quoted text, click to view] > Adrienne Boswell wrote: >> I am using MS SQL Server/ASP Classic and this is the query I am using >> now: >> >> SELECT distinct Category_Ipk AS category_id, category_description, >> classification_name, classification_ipk >> FROM view_product >> WHERE >> (Product_Search LIKE '%dog%' OR product_search LIKE '%dog%' > > What is the point of this? You're searching the same column twice ... > ? > Thanks for having a look, Bob. Yes, I realized that after I made the post. This is someone else's code that I'm cleaning up. [quoted text, click to view] > >> OR category_description LIKE '%dog%') >> AND City_id IN (16065) >> AND display_from <= '6/22/2006' and display_to>= '6/22/2006' >> UNION >> SELECT DISTINCT Category_Ipk as category_id, category_description, >> classification_name, classification_ipk >> FROM view_vendor_results_CA >> WHERE ( Vendor_Search LIKE '%dog%') >> AND City_id IN (16065) >> ORDER BY classification_ipk >> >> This query gives a list of categories in a certain city where there >> is a product or vendor whose name/description is like DOG. >> >> I am using another query somewhere else that is really fast and its >> using a LEFT OUTER JOIN so it gives me count of products/vendors. If >> the product count is 0, then I do one thing, if its more, then I do >> something else. This is much more efficient, because I am not having >> to go through a middle man. > > Much more efficient than what? What do you mean by "middle man"? Right now, the above query is sent to a category page, where the user clicks on a category, and it goes to a searching page, that display either products, or if there are no products, goes to a page that displays vendor names. It's very slow. If I have a count of products, then the user can click directly to the product page, and if no products, directly to the vendor list page. Ergo, no middle man. [quoted text, click to view] > > This looks like a situation tailor-made for a stored procedure, > depending on the "things" you are doing ... Yes, I'm sure it is. One of the other things I want to do is get away from dynamic SQL. [quoted text, click to view] > >> >> Here is my other query: > > I'm not clear ... is this a third query? or is it the query you > described in the previous paragraph? No, this is the second query, the one that's fast, that avoids the middleman so to speak. [quoted text, click to view] > >> SELECT vr.category_description AS description, vr.category_ipk AS >> cid, vr.classification_ipk AS clid, count(product_ipk) AS products, >> count(vr.vendor_ipk) AS vendors >> FROM view_vendor_results_CA vr WITH(NOEXPAND) > > Oh, so this is an indexed view ...? Yup. [quoted text, click to view] > >> LEFT OUTER JOIN >> view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk >> WHERE (vr.city_id = 18 AND vr.classification_ipk = 1) >> GROUP BY vr.category_description, vr.category_ipk, >> vr.classification_ipk >> ORDER BY vr.classification_ipk, vr.category_description >> >> I must be doing something wrong, because when I try to modify this to >> include search parameters, it is showing no records. I know in fact, >> that there are two records with the UNION query. >> > Hard to say without seeing your attempts to add search parameters. Are > you turning it into a stored procedure? As soon as I can get it working correctly, you betcha! If you want to see the site in action (as it is now) go to http://atlas.nextblock.com/files/index.asp?zip=18&cities=18 (I'm putting Glendale into the querystring for you to save a little time). I really appreciate all your help. -- Adrienne Boswell at Home Arbpen Web Site Design Services http://www.cavalcade-of-coding.info Please respond to the group so others can share
[quoted text, click to view]
Adrienne Boswell wrote: >>> >>> I am using another query somewhere else that is really fast and its >>> using a LEFT OUTER JOIN so it gives me count of products/vendors. >>> If the product count is 0, then I do one thing, if its more, then I >>> do something else. This is much more efficient, because I am not >>> having to go through a middle man. >> >> Much more efficient than what? What do you mean by "middle man"? > > Right now, the above query is sent to a category page, where the user > clicks on a category, and it goes to a searching page, that display > either products, or if there are no products, goes to a page that > displays vendor names. It's very slow. If I have a count of > products, then the user can click directly to the product page, and > if no products, directly to the vendor list page. Ergo, no middle > man. Ah! That makes it clearer, thanks. [quoted text, click to view] >>> >> Hard to say without seeing your attempts to add search parameters. >> Are you turning it into a stored procedure? > > As soon as I can get it working correctly, you betcha! If you want to > see the site in action (as it is now) go to > http://atlas.nextblock.com/files/index.asp?zip=18&cities=18 (I'm > putting Glendale into the querystring for you to save a little time). > But if I go there, I won't see the server-side code you are using to put the search parameters in, will I? or are you using xmlhttp ... ? -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
Bob Barrows [MVP] wote: [quoted text, click to view] > Adrienne Boswell wrote: > >>> > >>> I am using another query somewhere else that is really fast and its > >>> using a LEFT OUTER JOIN so it gives me count of products/vendors. > >>> If the product count is 0, then I do one thing, if its more, then I > >>> do something else. This is much more efficient, because I am not > >>> having to go through a middle man. > >> > >> Much more efficient than what? What do you mean by "middle man"? > > > > Right now, the above query is sent to a category page, where the user > > clicks on a category, and it goes to a searching page, that display > > either products, or if there are no products, goes to a page that > > displays vendor names. It's very slow. If I have a count of > > products, then the user can click directly to the product page, and > > if no products, directly to the vendor list page. Ergo, no middle > > man. > > > Ah! That makes it clearer, thanks. > > >>> > >> Hard to say without seeing your attempts to add search parameters. > >> Are you turning it into a stored procedure? > > > > As soon as I can get it working correctly, you betcha! If you want to > > see the site in action (as it is now) go to > > http://atlas.nextblock.com/files/index.asp?zip=18&cities=18 (I'm > > putting Glendale into the querystring for you to save a little time). > > > > But if I go there, I won't see the server-side code you are using to put the > search parameters in, will I? or are you using xmlhttp ... ? > No, you won't. But, I can tell you that it's just a plain old search box, going to a plain old classic ASP, that I am shoring up as we speak. For example: keyword = request.form("keyword") cities = request.querystring("cities") zip = request.querystring("zip") Right now, it's dynamic sql all the way. So those three parameters are what is going into the search right now. Bob, I can't thank you enough for all your help. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
Bob Barrows [MVP] wote:
[quoted text, click to view] > Adrienne Boswell wrote: > > > > For example: > > keyword = request.form("keyword") > > cities = request.querystring("cities") > > zip = request.querystring("zip") > > > > Right now, it's dynamic sql all the way. So those three parameters are > > what is going into the search right now. > > > > Bob, I can't thank you enough for all your help. > > I'm not sure what kind of help you need. Do you just want me to show you how > I would put them into the query you gave in the earlier post? If so (I'm > just guessing where the parameters need to go. I'm assuming keyword would be > dog and zip would be 16065, but where does cities fit into this query? > Anyways ... ): > > dim cn, sql, cmd, arParms, rs > set cn=createobject("adodb.connection") > cn.open " ... " > keyword = request.form("keyword") > 'validate this to make sure it contains no sql keywords > cities = request.querystring("cities") > 'ditto > zip = request.querystring("zip") > 'validate this with a regex > If DataIsValid then > sql="SELECT distinct Category_Ipk AS category_id, " & _ > "category_description,classification_name, " & _ > "classification_ipk FROM view_product " & _ > "WHERE (Product_Search LIKE ? OR " & _ > "category_description LIKE ?) AND City_id IN (16065) " & _ > "AND display_from <= '6/22/2006' and display_to>= " & _ > "'6/22/2006' UNION " & _ > "SELECT DISTINCT Category_Ipk as category_id, " & _ > "category_description,classification_name, classification_ipk " & _ > "FROM view_vendor_results_CA " & _ > "WHERE ( Vendor_Search LIKE '%dog%') " & _ > "AND City_id IN (16065) ORDER BY classification_ipk" > arParms=Array("%" & keyword & "%", "%" & keyword & "%", _ > zip, "%" & keyword & "%", zip) > set cmd=createobject("adodb.command") > with cmd > .CommandText=sql > .CommandType = 1 'adCmdText > set .ActiveConnection = cn > set rs = .Execute(,arParms) > End With > if not rs.eof then ... > end if > > > Thank you so much, Bob, but, that's not the one I really need help with. I need help with the outer join one. I gave you both queries so you could see what they both do. This is the one that I need to be able to put keyword into: SELECT vr.category_description AS description, vr.category_ipk AS cid, vr.classification_ipk AS clid, count(product_ipk) AS products, count(vr.vendor_ipk) AS vendors FROM view_vendor_results_CA vr WITH(NOEXPAND) LEFT OUTER JOIN view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk WHERE vr.city_id = " & zip & " AND vr.classification_ipk = " & classarr(i) GROUP BY vr.category_description, vr.category_ipk, vr.classification_ipk ORDER BY vr.classification_ipk, vr.category_description All I am giving this query is the zip (city) and classification id. The query loops through to get each classification - there are three. This is working beatifully, but when I try to filter it to a keyword, it gives either no results, or too many results. The fields in view_product are: product_search LIKE '%keyword%' AND display_from <= getdate() and display_to >= getdate() OR category_description LIKE '%keyword%' The fields in view_vendor_results are: vendor_search LIKE '%keyword%' This seems like it should be child's play, but for some reason I'm just not getting it. Do I really have to stay with a UNION? Again, I really appreciate all your help. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
[quoted text, click to view]
Adrienne Boswell wrote: > > For example: > keyword = request.form("keyword") > cities = request.querystring("cities") > zip = request.querystring("zip") > > Right now, it's dynamic sql all the way. So those three parameters are > what is going into the search right now. > > Bob, I can't thank you enough for all your help. I'm not sure what kind of help you need. Do you just want me to show you how I would put them into the query you gave in the earlier post? If so (I'm just guessing where the parameters need to go. I'm assuming keyword would be dog and zip would be 16065, but where does cities fit into this query? Anyways ... ): dim cn, sql, cmd, arParms, rs set cn=createobject("adodb.connection") cn.open " ... " keyword = request.form("keyword") 'validate this to make sure it contains no sql keywords cities = request.querystring("cities") 'ditto zip = request.querystring("zip") 'validate this with a regex If DataIsValid then sql="SELECT distinct Category_Ipk AS category_id, " & _ "category_description,classification_name, " & _ "classification_ipk FROM view_product " & _ "WHERE (Product_Search LIKE ? OR " & _ "category_description LIKE ?) AND City_id IN (16065) " & _ "AND display_from <= '6/22/2006' and display_to>= " & _ "'6/22/2006' UNION " & _ "SELECT DISTINCT Category_Ipk as category_id, " & _ "category_description,classification_name, classification_ipk " & _ "FROM view_vendor_results_CA " & _ "WHERE ( Vendor_Search LIKE '%dog%') " & _ "AND City_id IN (16065) ORDER BY classification_ipk" arParms=Array("%" & keyword & "%", "%" & keyword & "%", _ zip, "%" & keyword & "%", zip) set cmd=createobject("adodb.command") with cmd .CommandText=sql .CommandType = 1 'adCmdText set .ActiveConnection = cn set rs = .Execute(,arParms) End With if not rs.eof then ... end if -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
Bob Barrows [MVP] wote: [quoted text, click to view] > Adrienne Boswell wrote: > > Thank you so much, Bob, but, that's not the one I really need help > > with. I need help with the outer join one. I gave you both queries > > so > > you could see what they both do. This is the one that I need to be > > able to put keyword into: > > > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > > vr.classification_ipk AS clid, count(product_ipk) AS products, > > count(vr.vendor_ipk) AS vendors > > FROM view_vendor_results_CA vr WITH(NOEXPAND) > > LEFT OUTER JOIN view_product vp WITH(NOEXPAND) > > ON vr.vendor_ipk = vp.vendor_ipk > > WHERE vr.city_id = " & zip & " AND vr.classification_ipk = " & > > classarr(i) > > GROUP BY vr.category_description, vr.category_ipk, > > vr.classification_ipk > > ORDER BY vr.classification_ipk, vr.category_description > > > > All I am giving this query is the zip (city) and classification id. > > The query loops through to get each classification - there are three. > > This is working beatifully, but when I try to filter it to a keyword, > > it gives either no results, or too many results. > > > > The fields in view_product are: > > product_search LIKE '%keyword%' AND display_from <= getdate() and > > display_to >= getdate() OR category_description LIKE '%keyword%' > > The fields in view_vendor_results are: > > vendor_search LIKE '%keyword%' > > > > This seems like it should be child's play, but for some reason I'm > > just > > not getting it. Do I really have to stay with a UNION? Again, I > > really appreciate all your help. > > I don't understand why "union" is relevant for this query. > Does either view_vendor_results or view_product return columns named > product_search or category_description? If so, what you are doing should > work. > > -- view_product has product_search. view_vendor_results has vendor_search and both views have category_description -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
Adrienne Boswell wote: [quoted text, click to view] > Bob Barrows [MVP] wote: > > Adrienne Boswell wrote: > > > Thank you so much, Bob, but, that's not the one I really need help > > > with. I need help with the outer join one. I gave you both queries > > > so > > > you could see what they both do. This is the one that I need to be > > > able to put keyword into: > > > > > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > > > vr.classification_ipk AS clid, count(product_ipk) AS products, > > > count(vr.vendor_ipk) AS vendors > > > FROM view_vendor_results_CA vr WITH(NOEXPAND) > > > LEFT OUTER JOIN view_product vp WITH(NOEXPAND) > > > ON vr.vendor_ipk = vp.vendor_ipk > > > WHERE vr.city_id = " & zip & " AND vr.classification_ipk = " & > > > classarr(i) > > > GROUP BY vr.category_description, vr.category_ipk, > > > vr.classification_ipk > > > ORDER BY vr.classification_ipk, vr.category_description > > > > > > All I am giving this query is the zip (city) and classification id. > > > The query loops through to get each classification - there are three. > > > This is working beatifully, but when I try to filter it to a keyword, > > > it gives either no results, or too many results. > > > > > > The fields in view_product are: > > > product_search LIKE '%keyword%' AND display_from <= getdate() and > > > display_to >= getdate() OR category_description LIKE '%keyword%' > > > The fields in view_vendor_results are: > > > vendor_search LIKE '%keyword%' > > > > > > This seems like it should be child's play, but for some reason I'm > > > just > > > not getting it. Do I really have to stay with a UNION? Again, I > > > really appreciate all your help. > > > > I don't understand why "union" is relevant for this query. > > Does either view_vendor_results or view_product return columns named > > product_search or category_description? If so, what you are doing should > > work. > > > > -- > > view_product has product_search. > view_vendor_results has vendor_search > and both views have category_description > Here is the query I am using that is giving too many results: SELECT vr.category_description AS description, vr.category_ipk AS cid, vr.classification_ipk AS clid, count(product_ipk) AS products, count(vr.vendor_ipk) AS vendors FROM view_vendor_results_CA vr WITH(NOEXPAND) LEFT OUTER JOIN view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk WHERE (vr.city_id = 18 AND vr.classification_ipk = 2) AND (vp.Product_Search LIKE '%dog%' OR vr.Vendor_search LIKE '%dog%' OR vr.category_description LIKE '%dog%') GROUP BY vr.category_description, vr.category_ipk, vr.classification_ipk ORDER BY vr.classification_ipk, vr.category_description Like I said, I'm sure it's something really, really easy, I just can't see it. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
[quoted text, click to view]
Adrienne Boswell wrote: > Thank you so much, Bob, but, that's not the one I really need help > with. I need help with the outer join one. I gave you both queries > so > you could see what they both do. This is the one that I need to be > able to put keyword into: > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > vr.classification_ipk AS clid, count(product_ipk) AS products, > count(vr.vendor_ipk) AS vendors > FROM view_vendor_results_CA vr WITH(NOEXPAND) > LEFT OUTER JOIN view_product vp WITH(NOEXPAND) > ON vr.vendor_ipk = vp.vendor_ipk > WHERE vr.city_id = " & zip & " AND vr.classification_ipk = " & > classarr(i) > GROUP BY vr.category_description, vr.category_ipk, > vr.classification_ipk > ORDER BY vr.classification_ipk, vr.category_description > > All I am giving this query is the zip (city) and classification id. > The query loops through to get each classification - there are three. > This is working beatifully, but when I try to filter it to a keyword, > it gives either no results, or too many results. > > The fields in view_product are: > product_search LIKE '%keyword%' AND display_from <= getdate() and > display_to >= getdate() OR category_description LIKE '%keyword%' > The fields in view_vendor_results are: > vendor_search LIKE '%keyword%' > > This seems like it should be child's play, but for some reason I'm > just > not getting it. Do I really have to stay with a UNION? Again, I > really appreciate all your help. I don't understand why "union" is relevant for this query. Does either view_vendor_results or view_product return columns named product_search or category_description? If so, what you are doing should work. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
Bob Barrows [MVP] wote: [quoted text, click to view] > Adrienne Boswell wrote: > >> > >> view_product has product_search. > >> view_vendor_results has vendor_search > >> and both views have category_description > >> > > > > Here is the query I am using that is giving too many results: > > > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > > vr.classification_ipk AS clid, count(product_ipk) AS products, > > count(vr.vendor_ipk) AS vendors > > FROM view_vendor_results_CA vr WITH(NOEXPAND) > > LEFT OUTER JOIN > > view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk > > WHERE (vr.city_id = 18 AND vr.classification_ipk = 2) > > AND (vp.Product_Search LIKE '%dog%' OR vr.Vendor_search LIKE '%dog%' > > OR vr.category_description LIKE '%dog%') > > GROUP BY vr.category_description, vr.category_ipk, > > vr.classification_ipk > > ORDER BY vr.classification_ipk, vr.category_description > > > > > > Like I said, I'm sure it's something really, really easy, I just can't > > see it. > > I don't believe I'm going to be able to help without being able to reproduce > it. Could you provide DDL and sample data per > www.aspfaq.com/5006 > > Thank you, Bob. I will that to you as soon as possible, probably some time tonight after my kid has gone to bed. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
Bob Barrows [MVP] wote: [quoted text, click to view] > Adrienne Boswell wrote: > >> > >> view_product has product_search. > >> view_vendor_results has vendor_search > >> and both views have category_description > >> > > > > Here is the query I am using that is giving too many results: > > > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > > vr.classification_ipk AS clid, count(product_ipk) AS products, > > count(vr.vendor_ipk) AS vendors > > FROM view_vendor_results_CA vr WITH(NOEXPAND) > > LEFT OUTER JOIN > > view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk > > WHERE (vr.city_id = 18 AND vr.classification_ipk = 2) > > AND (vp.Product_Search LIKE '%dog%' OR vr.Vendor_search LIKE '%dog%' > > OR vr.category_description LIKE '%dog%') > > GROUP BY vr.category_description, vr.category_ipk, > > vr.classification_ipk > > ORDER BY vr.classification_ipk, vr.category_description > > > > > > Like I said, I'm sure it's something really, really easy, I just can't > > see it. > > I don't believe I'm going to be able to help without being able to reproduce > it. Could you provide DDL and sample data per > www.aspfaq.com/5006 > > Thank you, Bob. I will that to you as soon as possible, probably some time tonight after my kid has gone to bed. -- Adrienne Boswell at work Administrator nextBlock.com http://atlas.nextblock.com/files/ Please respond to the group so others can share
[quoted text, click to view]
Adrienne Boswell wrote: >> >> view_product has product_search. >> view_vendor_results has vendor_search >> and both views have category_description >> > > Here is the query I am using that is giving too many results: > > SELECT vr.category_description AS description, vr.category_ipk AS cid, > vr.classification_ipk AS clid, count(product_ipk) AS products, > count(vr.vendor_ipk) AS vendors > FROM view_vendor_results_CA vr WITH(NOEXPAND) > LEFT OUTER JOIN > view_product vp WITH(NOEXPAND) ON vr.vendor_ipk = vp.vendor_ipk > WHERE (vr.city_id = 18 AND vr.classification_ipk = 2) > AND (vp.Product_Search LIKE '%dog%' OR vr.Vendor_search LIKE '%dog%' > OR vr.category_description LIKE '%dog%') > GROUP BY vr.category_description, vr.category_ipk, > vr.classification_ipk > ORDER BY vr.classification_ipk, vr.category_description > > > Like I said, I'm sure it's something really, really easy, I just can't > see it. I don't believe I'm going to be able to help without being able to reproduce it. Could you provide DDL and sample data per www.aspfaq.com/5006 -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |