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

Groups > comp.databases.ms-sqlserver > #104 > unrolled thread

I Need help with a query...

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#104 — I Need help with a query...

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

[toc] | [next] | [standalone]

#107

On 05-04-2011 09:30, Alfaking wrote:
> hello.

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

Please post the query that you have used.

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

[toc] | [prev] | [next] | [standalone]

#108

On 5 Apr, 10:09, Henk van den Berg <hvandenb...@xs4all.nl> wrote:

> Please post the query that you have used.

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

[toc] | [prev] | [next] | [standalone]

#109

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:09, Henk van den Berg<hvandenb...@xs4all.nl>  wrote:
>
>> Please post the query that you have used.
>
> 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

[toc] | [prev] | [next] | [standalone]

#110

On 5 Apr, 10:59, Henk van den Berg <hvandenb...@xs4all.nl> wrote:
> Forgot to ask the following, which we need as well:
> Can you provide us with a CREATE TABLE script and a INSERT INTO script?

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

[toc] | [prev] | [next] | [standalone]

#111

Okay, I'll work with that.

On 05-04-2011 11:14, Alfaking wrote:
> On 5 Apr, 10:59, Henk van den Berg<hvandenb...@xs4all.nl>  wrote:
>> Forgot to ask the following, which we need as well:
>> Can you provide us with a CREATE TABLE script and a INSERT INTO script?
>
> 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...

[toc] | [prev] | [next] | [standalone]

#113

Am 05.04.2011 10:54, schrieb Alfaking:
> On 5 Apr, 10:09, Henk van den Berg<hvandenb...@xs4all.nl>  wrote:
>
>> Please post the query that you have used.
>
> In my idea this should be ok, but when I double-check the results,
> something goes wrong...
> no idea why

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

[toc] | [prev] | [next] | [standalone]

#116

On 5 Apr, 11:50, Lutz Uhlmann <n...@invalid.invalid> wrote:

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

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

[toc] | [prev] | [next] | [standalone]

#115

On 5 Apr, 12:13, Henk van den Berg <hvandenb...@xs4all.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...

[toc] | [prev] | [next] | [standalone]

#117

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:
> On 5 Apr, 12:13, Henk van den Berg<hvandenb...@xs4all.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...
>

[toc] | [prev] | [next] | [standalone]

#118

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' 

[toc] | [prev] | [next] | [standalone]

#119

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

[toc] | [prev] | [next] | [standalone]

#122

Henk van den Berg wrote:
> 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 

[toc] | [prev] | [next] | [standalone]

#123

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

>
> sorry - I should have tested

Been there, done that, got the T-shirt (I mean blame...)
:-D

>
>

[toc] | [prev] | [next] | [standalone]

#121

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web