hello.
I need help with a query please...
I've a table like this

Id | Contr | Type

the content of this table is like

Id | Contract | Type
1 | 1250 | W
2 | 1250 | T
3 | 5555 | W
4 | 6666 | T
5 | 7777 | W
6 | 6666 | W
7 | 6666 | W
8 | 6666 | T
9 | 7777 | W
10 | 1250 | W
11 | 1250 | T
12 | 1250 | W
13 | 1250 | T
14 | 1250 | T

the key is the ID

I need to extract all the "Contr" where type is not "T"
so i should have as result
3 | 5555 | W
5 | 7777 | W

whenever i found a type "T", I have to exclude that number of "contr".

I'm able to exclude all the records where type is not "T", but if I
found type ="T", that contr goes into results because of the ID
field...

any idea of how I can do this ?

thanks in advance

p.s.sorry for my english, I'm Italian. I really hope you can
understand me...

On 05-04-2011 09:30, Alfaking wrote:
Please post the query that you have used.

On 5 Apr, 10:09, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:

SELECT contr FROM table1
WHERE (contr NOT IN
(SELECT contr
FROM table1
WHERE (type = 'T')
GROUP BY contr)

In my idea this should be ok, but when I double-check the results,
something goes wrong...
no idea why

Forgot to ask the following, which we need as well:
Can you provide us with a CREATE TABLE script and a INSERT INTO script?

On 05-04-2011 10:54, Alfaking wrote:

On 5 Apr, 10:59, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:
I'm sorry, I can't...
that table is created by a software, and I need to extract some data
from a table that is already in place ....

suppose that I have that table, with data very similar to those I
wrote...

Okay, I'll work with that.

On 05-04-2011 11:14, Alfaking wrote:

On 05-04-2011 10:54, Alfaking wrote:

SELECT DISTINCT contr FROM table1 WHERE type <> 'T'
should do the trick

SELECT contr FROM table1 WHERE type <> 'T' GROUP BY contr
as an alternative

Am 05.04.2011 10:54, schrieb Alfaking:
When I run

SELECT *
FROM dbo.table1
WHERE contr NOT IN (SELECT contr FROM dbo.table1 WHERE (type = 'T')
GROUP BY contr)

i get this:
ID Contr Type
3 5555 W
5 7777 W
9 7777 W

I think this is the correct result!

Anyway there is also an alternative solution

SELECT t1.*
FROM dbo.table1 as t1 LEFT JOIN (SELECT contr FROM dbo.table1 WHERE
(type = 'T') GROUP BY contr) AS t2 ON t1.contr=t2.contr
WHERE t2.contr IS NULL

On 05-04-2011 10:54, Alfaking wrote:

It's one of those cases where there's a subtle difference between

"give me everything where condition <X> applies"

yields a different result from

"leave out everything where condition <X> does not apply"

In other words, all squares are geometric shapes, but not all geometric
shapes are squares.


If you

SELECT contr FROM table1
WHERE (contr IN
(SELECT contr
FROM table1
WHERE (type <> 'T')
GROUP BY contr)


you will get the expected results.

Note the difference from your version:
WHERE (contr NOT IN ... type = 'T' )

as opposed to
WHERE ( contr IN ... type <> 'T' )


Still, the simple version is preferable

SELECT DISTINCT contr FROM table1 WHERE type <> 'T'

On 5 Apr, 12:13, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:

Not sure about this...
if I

i get also a number of "contr" where type='T', because of the
different ID (that's the key).
for example, in the results I have also

1250 because of the first line of the table...
but that's not correct !!!

again, wrong results...