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
Lou Zucaro <lou (AT) netspecialists (DOT) com> wrote

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

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
Lou Zucaro <lou (AT) netspecialists (DOT) com> wrote