sql server mseq: Query Help Requested -- sql server mseq

Re: Query Help Requested

Lou Zucaro
7/27/2003 6:49:53 AM

Hello,

Thanks for your answer, but I'm wondering, will what you
wrote here work as a SELECT statement in an ASP script
that's pulling data from an Access database on a website?
I probably should have mentioned my platform in my
original post...sorry about that.

LZ

[quoted text, click to view]

Re: Query Help Requested

Lou Zucaro
7/27/2003 7:04:57 AM

Hi again,

Actually, I think I figured it out...I got rid of the
first two lines (since I already have the variable set up
in my ASP code) and added an AND between the last two
lines. So the final sql statement looks like this
(modified a bit with actual field names):

sql = "SELECT ProductID, ProductName FROM Products sqlA
WHERE NOT EXISTS (SELECT 1 FROM GroupProducts sqlB WHERE
sqlB.lngProductID = sqlA.ProductID AND sqlB.lngGroupID
= " & lngGroupID & ");"

It all makes sense to me except the 'SELECT 1' in the
second part of the query...what does that provide? Does
it make sure the query doesn't return duplicates?

Thanks again for your help...VERY much appreciated!!

LZ

[quoted text, click to view]

Re: Query Help Requested

Vishal Parkar
7/27/2003 4:34:15 PM

Try:
declare @v_lnggroupid int
select @v_lnggroupid = 2
select lngID, strProduct
from products a
where not exists
(select 1 from groupproducts b
where
b.lngproductid = a.lngID
b.lnggroupid = @v_lnggroupid)

--
-Vishal
[quoted text, click to view]

Re: Query Help Requested

Vishal Parkar
7/27/2003 9:05:49 PM

your SQL statement looks correct . what i 've posted is from the view point
of T-SQL.
First two lines are T-SQL syntaxes. hence there must be something equivalent
in ASP as well.
(I think you are through with it.)

[quoted text, click to view]

The "SELECT 1...." is a subquery which checks for the existance of the row
on the basis
of joins "sqlB.lngProductID = sqlA.ProductID" . so if match is found it will
return a row(here 1) else no
rows will be returned by the subquery, on the basis of the rows returned by
the subquery
your outer table will return the matching rows. It is no way related to the
returning duplicates.
However if your subquery returns two rows for a single "lngProductID" and if
your outer table
ie Products has only one row for the corresponding "lngProductID" then
outer query will
return only one row. but if Products table has more than one row a
particular "productid"
then you will have to use DISTINCT clause in SELECT statment to ignore
duplicates.

HTH

--
-Vishal
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.