I Need help with a query...

#11

On 5 Apr, 11:50, Lutz Uhlmann <n... (AT) invalid (DOT) invalid> wrote:

Quote:

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!

selecting only CONTR and not * and with the "group by contr "clause,
yes, it definitely works...

Quote:

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

yes, I've double checked this a lot of times, and this query works too

SELECT t1.CONTR
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)
GROUP BY t1.CONTR

I don't know why, maybe it depends on the db structure, but I'm still
getting some wrong results...
unfortunately, the db is much more complex than 1 simple table, so I
have to double check some links to other table I think

Thanks to everybody for the help that you gave me, it has been really
useful.
thank you so much !

#12

Yes, I re-read your question and I found out that I had misinterpreted
it. Therefore I canceled my answers.

Best,
Henk

On 05-04-2011 12:57, Alfaking wrote:

Quote:

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

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.

Not sure about this...
if I

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

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

Still, the simple version is preferable
SELECT DISTINCT contr FROM table1 WHERE type<> 'T'

again, wrong results...

#13

Alfaking wrote:

Quote:

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

Are 'W' and "T" the only allowable types? If so:

SELECT min(id),Contr,max(Type)
FROM table
GROUP BY Contr
HAVING max(Type)<>'T'

#14

On 05-04-2011 13:17, Bob Barrows wrote:

Quote:

Are 'W' and "T" the only allowable types? If so:

SELECT min(id),Contr,max(Type)
FROM table
GROUP BY Contr
HAVING max(Type)<>'T'

When I run that, the resultset contains 1250 which should be excluded
since there's a row with 1250, T

#15

Alfaking wrote:

Quote:

hello.
I need help with a query please...
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 ?

Just to follow up, if other contract types exist, and you need to retrieve
all IDs and types for the contracts that don't have a T type, then this will
do it:

select id,contr,type
from table t
where not exists (
select * from table where
contr = t.contr and type = 'T')

#16

Henk van den Berg wrote:

Quote:

On 05-04-2011 13:17, Bob Barrows wrote:

Are 'W' and "T" the only allowable types? If so:

SELECT min(id),Contr,max(Type)
FROM table
GROUP BY Contr
HAVING max(Type)<>'T'

When I run that, the resultset contains 1250 which should be excluded
since there's a row with 1250, T

Oh duh! It should have been

HAVING min(type) <> 'T'

sorry - I should have tested