Help with query

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%' 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

Re: Help with query

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 ... ?


> 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 ...

>
> 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?

> 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 ...?

> 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?
--
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"

Re: Help with query

Gazing into my crystal ball I observed "Bob Barrows [MVP]"
writing in
news:essjiFmlGHA.5036@TK2MSFTNGP05.phx.gbl:

> 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.

>
>> 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.

>
> 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.

>
>>
>> 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.

>
>> 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.

>
>> 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

Re: Help with query

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 ... ?



--
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"

Re: Help with query

Bob Barrows [MVP] wote:
> 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

Re: Help with query

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"

Re: Help with query

Bob Barrows [MVP] wote:
> 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

Re: Help with query

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"

Re: Help with query

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

--
Adrienne Boswell at work
Administrator nextBlock.com
http://atlas.nextblock.com/files/
Please respond to the group so others can share

Re: Help with query

Adrienne Boswell wote:
> 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

Re: Help with query

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"

Re: Help with query

Bob Barrows [MVP] wote:
> 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

Re: Help with query

Bob Barrows [MVP] wote:
> 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