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
Hi
This will depend on the values of NoDossier that you have in the two tables!
You have 3000 matches but this could be (say) 2 distinct values for NoDossier.
John
"Fernand St-Georges" wrote:

> 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
>
>
|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)
|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)

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].NoDossi
er
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 problemHi
This will depend on the values of NoDossier that you have in the two tables!
You have 3000 matches but this could be (say) 2 distinct values for NoDossie
r.
John
"Fernand St-Georges" wrote:

> 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].NoDos
sier
> 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
>
>|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)sql

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 problemHi
This will depend on the values of NoDossier that you have in the two tables!
You have 3000 matches but this could be (say) 2 distinct values for NoDossier.
John
"Fernand St-Georges" wrote:
> 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
>
>|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)|||Fernand St-Georges
is this what you want?
SELECT
tblClient.*
FROM
tblClient
WHERE
tblClient.NoDossier NOT IN (
SELECT
NoDossier
FROM
[new table]
)

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