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

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

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
Asked:
COBOLforever
  • 9
  • 6
  • 3
1 Solution
 
lwadwellCommented:
is this it?

SELECT *
FROM your_table
WHERE COMP <> 'NON' AND TYPE = 'WN'
0
 
lwadwellCommented:
>> 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
 
slightwv (䄆 Netminder) 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
/

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
COBOLforeverAuthor 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
 
slightwv (䄆 Netminder) Commented:
Who's post was that a response to?
0
 
COBOLforeverAuthor Commented:
Iwadwell

I don't get your post
0
 
COBOLforeverAuthor Commented:
for slightwv -
maybe if you preceded the whole clause with
select id from table where id in
(your select clause...
?
0
 
slightwv (䄆 Netminder) 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
 
lwadwellCommented:
perhaps

SELECT DISTINCT id
FROM table
WHERE id NOT IN (SELECT id FROM table WHERE COMP <> 'NON' AND TYPE <> 'WN')
0
 
slightwv (䄆 Netminder) 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
 
COBOLforeverAuthor 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
 
slightwv (䄆 Netminder) 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
 
COBOLforeverAuthor 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
 
COBOLforeverAuthor Commented:
so stoopid - I added a where verb instead of another AND
0
 
COBOLforeverAuthor Commented:
it ran and I am validating results now
0
 
COBOLforeverAuthor 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
 
slightwv (䄆 Netminder) 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
 
COBOLforeverAuthor 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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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