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

To 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/techinf...2000/books.asp