471,050 Members | 1,387 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

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 problem
Jul 23 '05 #1
4 5869

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 ***
Jul 23 '05 #2


text, ntext or image cannot be selected as distinct

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #3
You should have mentioned that earlier!
*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #4
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
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by paul.jameson | last post: by
2 posts views Thread by Arsen V. | last post: by
2 posts views Thread by Mirnes | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.