sql LEFT JOIN and IN question

[niceguy]

I'm trying to select records from two tables. the following code works for
what i want to to:

set RSMain = conn.execute ( "select top 20 product, prodcode, edition, (
select count(id) from cartTemp where convert(varchar (4000),cartdeta ils) in
(a.prodcode)) as counter from Products a where isOffer = 0 order by
prodcode")
do until RSMain.EOF

set RSCheck = conn.execute ("select count(id) as counter from cartTemp where
cartdetails like '%" & RSMain("prodcod e") & "|%' ")

if NOT RSCheck.EOF then if RSCheck("counte r") > 0 then response.write
RSMain("product ") & ": " & RSMain("edition ") & " is in " & RSCheck("counte r")
& ":" & RSMain("counter ") & " shopping carts. "

RSMain.movenext
loop

but i was hoping to be able to simplify it thusly to avoid the second lookup

set RSMain = conn.execute ( "select top 20 product, prodcode, edition,
count(b.id) as counter from Products a join cartTemp b on
convert(varchar (4000),b.cartde tails) in (a.prodcode) where isOffer = 0 group
by a.product,a.edi tion,a.prodcode order by prodcode")

which doesn't fail, but returns a 0 count on the RSMAin("counter ") ...

I guess i'm trying to use "in" as a replacement for "like '%ANYQUERY%', but
of course i don't yet know what ANYQUERY is...

any thoughts greatly appreciated

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

What you did post is confusing. For example, you have the vague name
"id" without tellling us what it identifies, you use aliases that make
no sense to the reader, cart_details is plural so it cannot be a data
element (it seems to be prod_code under a second name), isoffer looks
like you are writing SQL with Boolean bit flags, I can only hope that
CartTemp is not a temporary table, etc. Ihave teh feeling thatyou are
writing a tape file system where you build a query in procedural steps
and not a relational query at all.

And you never told us what you want to do.

The best I can pull out and put into SQL is:

SELECT P.product_name, P.product_editi on, P.prod_code,
COUNT(C.prod_co de) AS cart_tally
FROM Products AS P,
CartTemp AS C
WHERE C.prod_code = P.prod_code
AND P.isoffer = 0
GROUP BY P.product_name, P.product_editi on, P.prod_code;

I am guessing that you want the tally of how many times a product was
in CartTemp. You do not need loops for that.

[Erland Sommarskog]

niceguy (ni*****@hotmai l.com) writes:

but i was hoping to be able to simplify it thusly to avoid the second
lookup

set RSMain = conn.execute ( "select top 20 product, prodcode, edition,
count(b.id) as counter from Products a join cartTemp b on
convert(varchar (4000),b.cartde tails) in (a.prodcode)
where isOffer = 0
group by a.product,a.edi tion,a.prodcode order by prodcode")

which doesn't fail, but returns a 0 count on the RSMAin("counter ") ...

I guess i'm trying to use "in" as a replacement for "like '%ANYQUERY%',
but of course i don't yet know what ANYQUERY is...

I've been looking at this for several minutes now, but I've come to the
conclusion that I have only have information enough for bad guesses. So
I suggest that you post:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired output given the sample.
o A short narrative of what you want to achieve.

This is the standard recommendation when people ask for help with
queries. By including all of these you can get a tested query in
response. The less you include, the less you get in response, both
in terms of quantity and quality.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

In addition to the comments of Joe and Erland I would like to know why
you have dynamic SQL running in your VB app instead of using stored
procedures. I hope you understand the implications of what you have
done.

--
David Portas
SQL Server MVP
--

[niceguy]

On Sun, 20 Feb 2005 00:14:44 +0000, David Portas wrote
(in article <11************ ********@f14g20 00cwb.googlegro ups.com>):

In addition to the comments of Joe and Erland I would like to know why
you have dynamic SQL running in your VB app instead of using stored
procedures. I hope you understand the implications of what you have
done.


....

On Sat, 19 Feb 2005 22:08:36 +0000, CELKO-- wrote
(in article <11************ **********@z14g 2000cwz.googleg roups.com>):
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

thanks for your replies - I'll try to rephrase the query sensibly in a
reply... By the way, the answer is no, I don't know the implications - all i
know is stored procedures are a pain in the proverbial - what are the
implications? When i do speed comparisons between dynamic and stored
procedures the speed is the same.

But I'm pretty ignorant of the technical side :(

[Erland Sommarskog]

niceguy (ni*****@hotmai l.com) writes:

thanks for your replies - I'll try to rephrase the query sensibly in a
reply... By the way, the answer is no, I don't know the implications -
all i know is stored procedures are a pain in the proverbial - what are
the implications? When i do speed comparisons between dynamic and stored
procedures the speed is the same.

Whether stored procedures are a pain anywhere depends a little on your
architcure and your application. The main issue here is not performance,
but security, from two points of view: 1) general permissions 2) SQL
injection.

General permissions: Having the SQL code in the VB code, means that the user
that runs the VB code must have permissions to access the tables. This means
that if this users gets access to the database by other means than the
application, he may be able to access and update data in ways that he maybe
shouldn't.

Now, this may or may not be an issue. Here are at three possible reasons
why it might not: a) Your code runs in a middle tier that authenticate
the users, and then logs into the into SQL Server with a general login,
and the users does not even have direct permission to the database. b)
you are using application roles, and all rights have been granted to the
application role. c) business rules are such that users are permitted to
access the database directly anyway, and your application is just a helper
for them.

2) SQL injection. Even if a) or b) applies to you, you still have to worry
about SQL injection. In the sample you posted the risk for SQL injection
is low, since data you insert comes from the database. But consider:

set RSCheck = conn.execute ("select count(id) as counter from cartTemp
where cartdetails like '%" & RSMain("prodcod e") & "|%' ")

Assume now that prodcode contains a single quote. Assume further that
the single quote is followed by " DROP TABLE tbl --". You will then have
an SQL batch that performs something that you intended. Of course, that
product code would like this is unlikely, but assume instead input from
a malicious user. Even if you don't have a mailicious user, assume that
you have a user who tries to enter the name O'Brien.

The way to address SQL injection is not stored procedure per se, but to
use parameterised commands. See this link for a quick example:
http://authors.aspalliance.com/steve...les/sprocs.asp. Although
this example uses stored procedure, you can to the same with bare SQL
statements as well by using ? as placeholder for parameters.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lee Tudor]

select top 20 product, prodcode, edition, count(b.id) as counter from
Products a
join cartTemp b on convert(varchar (4000),b.cartde tails) LIKE
'%'+cast(a.prod code as varchar)+'|%'
where isOffer = 0
group a.product, a.edition, a.prodcode order by prodcode

this mirrors what you are trying to acheive with the code you use in your
inner loop and the join will eliminate products with no cart records
removing the need for the check on counter>0 (substitute left join to put
them back in), although there are a few notes.

1) you will have trouble if you have a product 'abcd' and a product 'bcd'
as checking for carts containing 'bcd' will count carts containing 'abcd'.
To solve this with minimum modification to the code, put a prefix as well as
a suffix on the comparison:

'|'+convert(var char(4000),b.ca rtdetails) LIKE '%|'+cast(a.pro dcode as
varchar)+'|%'

2) using cartdetails in this way to store a list of items makes manipulation
more difficult then necessary and <B>Criples</B> performace. Consider using
the carttemp table for the cart itself and a cartitem table representing the
entities within the cart.

3) stored procedures are better both for performance and security reasons
than raw T-SQL. I started my coding life asp writing code similar to your
example, over time through experience and understanding, I moved completely
to stored procedures and there is absolutely no downside to the migration.

Mr Tea

"niceguy" <ni*****@hotmai l.com> wrote in message
news:00******** *************** ******@news.ast raweb.com...

I'm trying to select records from two tables. the following code works for
what i want to to:

set RSMain = conn.execute ( "select top 20 product, prodcode, edition, (
select count(id) from cartTemp where convert(varchar (4000),cartdeta ils)
in
(a.prodcode)) as counter from Products a where isOffer = 0 order by
prodcode")
do until RSMain.EOF

set RSCheck = conn.execute ("select count(id) as counter from cartTemp
where
cartdetails like '%" & RSMain("prodcod e") & "|%' ")

if NOT RSCheck.EOF then if RSCheck("counte r") > 0 then response.write
RSMain("product ") & ": " & RSMain("edition ") & " is in " &
RSCheck("counte r")
& ":" & RSMain("counter ") & " shopping carts. "

RSMain.movenext
loop

but i was hoping to be able to simplify it thusly to avoid the second
lookup

set RSMain = conn.execute ( "select top 20 product, prodcode, edition,
count(b.id) as counter from Products a join cartTemp b on
convert(varchar (4000),b.cartde tails) in (a.prodcode) where isOffer = 0
group
by a.product,a.edi tion,a.prodcode order by prodcode")

which doesn't fail, but returns a 0 count on the RSMAin("counter ") ...

I guess i'm trying to use "in" as a replacement for "like '%ANYQUERY%',
but
of course i don't yet know what ANYQUERY is...

any thoughts greatly appreciated

[--CELKO--]

>> all I know is stored procedures are a pain in the proverbial - what
are the implications? <<

No, they are pretty easy in T-SQL. It is a small language designed for
such short jobs inside the database. The syntax is from the Algol
family by way of C.

When I do speed comparisons between dynamic and stored procedures

the speed is the same. <<

Wait until you scale up and have network between the front end the
database. You will see a large difference. You also get better
control over the user access and better data integrity.

[David Portas]

See Erland's reply.

Also note that putting your data access code on the server simplifies
maintenance. You won't have to re-test and re-deploy your application
for every schema change.

Stored procedures shouldn't be a pain. They are the most powerful
tool at your disposal for data access purposes.

--
David Portas
SQL Server MVP
--