Groups > comp.databases.ms-sqlserver > #118

Re: I Need help with a query...

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: I Need help with a query...
Date	Tue, 5 Apr 2011 07:17:59 -0400
Organization	A noiseless patient Spider
Lines	53
Message-ID	<inetog$514$1@dont-email.me> (permalink)
References	<ab761644-26ae-41c4-a599-acb60a80ae69@n12g2000yqc.googlegroups.com>
Injection-Date	Tue, 5 Apr 2011 11:17:36 +0000 (UTC)
Injection-Info	mx02.eternal-september.org; posting-host="inlgCTpOMxujS+VHkVo6dA"; logging-data="5156"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+QicOiMuXPwANjEELr77J1WORFr2P/IZ0="
X-MimeOLE	Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646	Format=Flowed; Original
X-Antivirus-Status	Clean
X-Newsreader	Microsoft Outlook Express 6.00.2900.5931
X-Antivirus	avast! (VPS 110404-1, 04/04/2011), Outbound message
Cancel-Lock	sha1:U3gTyQgKPv+Xffsh3oL11JD3G2o=
X-Priority	3
X-MSMail-Priority	Normal
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:118

Show key headers only | View raw

Alfaking wrote:
> 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'

Thread

I Need help with a query... Alfaking <favia.alessandro@gmail.com> - 2011-04-05 00:30 -0700
  Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 10:09 +0200
    Re: I Need help with a query... Alfaking <favia.alessandro@gmail.com> - 2011-04-05 01:54 -0700
      Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 10:59 +0200
        Re: I Need help with a query... Alfaking <favia.alessandro@gmail.com> - 2011-04-05 02:14 -0700
          Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 11:29 +0200
      Re: I Need help with a query... Lutz Uhlmann <news@invalid.invalid> - 2011-04-05 11:50 +0200
        Re: I Need help with a query... Alfaking <favia.alessandro@gmail.com> - 2011-04-05 04:04 -0700
      Re: I Need help with a query... Alfaking <favia.alessandro@gmail.com> - 2011-04-05 03:57 -0700
        Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 13:07 +0200
  Re: I Need help with a query... "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-05 07:17 -0400
    Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 13:33 +0200
      Re: I Need help with a query... "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-05 07:40 -0400
        Re: I Need help with a query... Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-05 14:26 +0200
  Re: I Need help with a query... "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-04-05 07:38 -0400

csiph-web