exclude query

I have 16,000 rows in tblClient and 3000 rows in NewTable.

SELECT tblClient.*
FROM tblClient INNER
JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier

if I use = (equal) instead of <> (exclude), the query returns 3000 rows

when I use <> it returns 160000 rows,
if I try group by, the query bugs

what is my problemTo eliminate duplicate rows use:
-----------------

SELECT DISTINCT tblClient.*
FROM tblClient INNER
JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier

To use group by:
------------------

SELECT DISTINCT tblClient.col1, tblClient.col2, .....
FROM tblClient INNER
JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier
GROUP BY tblClient.col1, tblClient.col2,......

*** Sent via Developersdex http://www.developersdex.com ***|||
text, ntext or image cannot be selected as distinct

*** Sent via Developersdex http://www.developersdex.com ***|||You should have mentioned that earlier!

*** Sent via Developersdex http://www.developersdex.com ***|||Fernand St-Georges (fernand.st-georges@.videotron.ca) writes:
> I have 16,000 rows in tblClient and 3000 rows in NewTable.
> SELECT tblClient.*
> FROM tblClient INNER
> JOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossier
> if I use = (equal) instead of <> (exclude), the query returns 3000 rows
> when I use <> it returns 160000 rows,
> if I try group by, the query bugs
> what is my problem

Using <> as a joining operator is very rarely useful. Say that the number
of rows in tblClient is 163 and in [New Table] have 1000 rows. That makes
up for a total of 163000 possisble combinations. You condition filters out
those 3000 where NoDossier are equal.

What you probably want is:

SELECT c.*
FROM tblClient c
WHERE NOT EXISTS (SELECT *
FROM [New Table] n
WHERE c.NoDossier = n.NoDossier)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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