[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 394

# SQL - Simple

Table structure is:

ID(3)
TYPE(2)
COMP(3)

Example Data
123     XX    NON
123    WN    GRD
123    WN    GRD
124    WN     GRD
124     XX       GRD
125    WN     GRD
125    WN      GRD

Want to pull the ID's where if you exclude rows where COMP=NON, all the remaining rows are TYPE = WN.

So I would end up with ID's
123 (because all their rows that are not NON, have TYPE=WN
125 (because all their rows are TYPE=WN and they have no NON rows
but not 124 because they have an XX Type on a row that is not NON

Yes? Probably some sort of count with a having clause...

Thanks
0
COBOLforever
• 9
• 6
• 3
1 Solution

Commented:
is this it?

SELECT *
FROM your_table
WHERE COMP <> 'NON' AND TYPE = 'WN'
0

Commented:
>> but not 124 because they have an XX Type on a row that is not NON

This has me puzzled.  You had two rows for 124
124    WN     GRD
124     XX       GRD
One of which is WN and not NON ... doesn't that meet the criteria?
0

Commented:
>>is this it?

Given the test case below, that returns id 124.

Try this:

``````drop table tab1 purge;
create table tab1(
ID char(3),
TYPE char(2),
COMP char(3)
);

insert into tab1 values('123','XX','NON');
insert into tab1 values('123','WN','GRD');
insert into tab1 values('123','WN','GRD');
insert into tab1 values('124','WN','GRD');
insert into tab1 values('124','XX','GRD');
insert into tab1 values('125','WN','GRD');
insert into tab1 values('125','WN','GRD');
commit;

select id
from
(
select id,
sum(case when type='WN' then 1 end) WN_TYPES,
count(type) TYPE_COUNT
from tab1 where comp <> 'NON'
group by id
)
where wn_types=type_count
/
``````
0

Author Commented:
I only want the ID once so maybe a distinct on that but 124 is not selected be not ALL of their rows are WN
0

Commented:
Who's post was that a response to?
0

Author Commented:

0

Author Commented:
for slightwv -
maybe if you preceded the whole clause with
select id from table where id in
?
0

Commented:
>>maybe if you preceded the whole clause with

Did you run my test?

I got your desired results with what I posted.  If it doesn't work for you, please add to it to show where it fails.
0

Commented:
perhaps

SELECT DISTINCT id
FROM table
WHERE id NOT IN (SELECT id FROM table WHERE COMP <> 'NON' AND TYPE <> 'WN')
0

Commented:
>>perhaps

It does produce the same results as mine but hits the table twice and based on the small test case I posted, the execution plan is a lot higher than mine.

Since I'm not 100% confident on the requirements, I did not scale it up to see if that trend continues.
0

Author Commented:
slightwv - did you run my test?>>

see that's the crappy part - - I do not have update access so cannot create and test scenarios that might help me help myself....

you said that your example gave 124 when I did not want 124 but your last post says that it gives the results I wanted. I will try against my data.  brb.
0

Commented:
>>you said that your example gave 124 when I did not want 124

I was commenting that  given my test case, lwadwell's post in http:#a38294642 returned 124.

Why would I post something that knowingly went against your requirements?
0

Author Commented:
that may work but I need to insert additional overall criteria - - I tried inserting a simple two element WHERE clause just before the GROUP BY ID but I am getting that it has a mssing parenthesis at line 8 (my new line)
0

Author Commented:
so stoopid - I added a where verb instead of another AND
0

Author Commented:
it ran and I am validating results now
0

Author Commented:
it seems ok but I really cannot find any id's that re returned where they have more than one row in the database so I am not absolutely certain. Your SQL looks good and I am pretty sure it would not only select id's with only a single row so I am awarding and continuing to validate.

Thanks. You should try to no be so touchy on comments though. It very well could be the case that you could have either mis-typed or misunderstood my original request.

Thanks again
0

Commented:
I apologize and probably should have rephrased my comment.

>>I really cannot find any id's that re returned where they have more than one row in the database so I am not absolutely certain

If this question isn't complete would you like to have it reopened until you finalize an answer that works for you?

I'm afraid that any followup question would likely be considered a duplicate and violate the site rules.

>>I do not have update access so cannot create and test scenarios that might help me help myself....

Do you not have a test machine where you can install OracleXE?  Having the ability to test different things is a must.
0

Author Commented:
Thanks for taking my comment in the manner intended. I have other experts on site today that were not available last night so I shuold be good. I am going to ck into OracleXE thanks.
0

## Featured Post

• 9
• 6
• 3
Tackle projects and never again get stuck behind a technical roadblock.