exclude query

[Fernand St-Georges]

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

[debian mojo]

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 ***

[Fernand St-Georges]

text, ntext or image cannot be selected as distinct

*** Sent via Developersdex http://www.developersdex.com ***

[debian mojo]

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

[Erland Sommarskog]

Fernand St-Georges (fe****************@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, es****@sommarskog.se

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