Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: I Need help with a query...

Show all headers | 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' 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

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