Home » SQL & PL/SQL » SQL & PL/SQL » Need help to write a SQL (ORACLE 10g)
icon5.gif  Need help to write a SQL [message #391325] Wed, 11 March 2009 17:23 Go to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Hi All,

I need help to write a query with the following data :

I have many columns in the table but my requirement basically involves these four columns

ID TEAM EXT_ID TYPE

1 A 12 N
2 B 14 N
3 A 12 Y
4 A 13 Y
5 C 15 N
6 A 12 SP

With the above data I need to find
which group of team and ext_id has only type 'N'

in the above example

TEAM "A" and EXT_ID "12" has type "N","Y","SP"
TEAM "B" and EXT_ID "14" has type "N"
TEAM "A" and EXT_ID "13" has type "Y"
TEAM "C" and EXT_ID "15" has type "N"


The expected output of the query should be
TEAM "B" and EXT_ID "14" has type "N"
TEAM "C" and EXT_ID "15" has type "N"


as these have type as "N" only


Your help is appreciated.
Re: Need help to write a SQL [message #391326 is a reply to message #391325] Wed, 11 March 2009 17:29 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to use NOT IN or NOT EXISTS
Re: Need help to write a SQL [message #391327 is a reply to message #391325] Wed, 11 March 2009 17:30 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
A solution could include MINUS
Re: Need help to write a SQL [message #391330 is a reply to message #391327] Wed, 11 March 2009 17:39 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Thanks for the inputs but I am not able to frame the where clause

Select A.* from table1 A, table1 B
where A.EXT_ID = B.EXT_ID
and A.ID <> B.ID
and A.TEAM = B.Team

I am lost how to include the clause for type

Please guide
Re: Need help to write a SQL [message #391331 is a reply to message #391325] Wed, 11 March 2009 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Please guide
You need to use NOT IN or NOT EXISTS
or
A solution could include MINUS
Re: Need help to write a SQL [message #391334 is a reply to message #391331] Wed, 11 March 2009 17:57 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Thanks Sir\Mam,

I did try this

Select C.* from table1 C
Minus
Select A.* from table1 A, table1 B
where A.EXT_ID = B.EXT_ID
and A.ID <> B.ID
and A.TEAM = B.Team
and (A.type = 'Y' or A.type = 'SP')

but the result is wrong the o\p include

those with only one entry for "Y" and "SP" also

as this is an urgent requirement will appreciate more hints

Thanks In advance

Re: Need help to write a SQL [message #391336 is a reply to message #391325] Wed, 11 March 2009 18:09 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're using minus then you want to select team and ext_ids that have type N
minus teams and ext_ids that have some other type.

You don't need to access table1 three times.
Also minus only removes rows where all the columns have the same values - you're selecting too many.

Read the documentation found here:
http://tahiti.oracle.com/
It's got examples.
Re: Need help to write a SQL [message #391340 is a reply to message #391336] Wed, 11 March 2009 18:57 Go to previous messageGo to next message
ksuchetan
Messages: 15
Registered: March 2009
Junior Member
Thanks a bunch...this really was helpful
Re: Need help to write a SQL [message #391341 is a reply to message #391325] Wed, 11 March 2009 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
The Posting Guidelines, which you should read & follow include:
"If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it."
Re: Need help to write a SQL [message #391345 is a reply to message #391325] Wed, 11 March 2009 20:52 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Can we do something like this ?? I am not sure about the best approach(Performance wise), but i just did it..

SELECT ID, team, ext_id, TYPE
  FROM extension
 WHERE TYPE = 'n' AND ext_id IN (SELECT   ext_id
                                     FROM extension
                                 GROUP BY ext_id
                                   HAVING COUNT (*) = 1)


Provide Suggestions to improve..

Regards,
Ashoka BL
Re: Need help to write a SQL [message #391415 is a reply to message #391325] Thu, 12 March 2009 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd need team in the subquery as well.
and shouldn't it be COUNT(*) > 1?
Re: Need help to write a SQL [message #391442 is a reply to message #391415] Thu, 12 March 2009 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NO, I think COUNT(*)=1 is right.

@Ashoka_bl's query looks for all teams that have a Type of 'N' and belong to the set of teams who only have a single entry in the table.

You could use sonething like Tom Kytes STRAGG function to get a list of all the types for each team/ext_id.

You could do it analytically:
create table test_141 (ID number, TEAM varchar2(1), EXT_ID number, TYP varchar2(2));
 
insert into test_141 values ( 1, 'A', 12, 'N');
insert into test_141 values ( 2, 'B', 14, 'N');
insert into test_141 values ( 3, 'A', 12, 'Y');
insert into test_141 values ( 4, 'A', 13, 'Y');
insert into test_141 values ( 5, 'C', 15, 'N');
insert into test_141 values ( 6, 'A', 12, 'SP');

select team,ext_id
from  (select team
             ,ext_id
             ,max(case when typ = 'N' then 1 else 0 end) over (partition by team,ext_id order by null) has_n
             ,count(typ) over (partition by team,ext_id) total_types
       from   test_141)
where has_n = 1
and   total_types = 1; 


Re: Need help to write a SQL [message #391445 is a reply to message #391442] Thu, 12 March 2009 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it with a single group by:
select team,ext_id
from   test_141
group by team,ext_id
having sum(case when typ = 'N' then 1 else 0 end) = 1
and    count(typ) = 1;
Re: Need help to write a SQL [message #391459 is a reply to message #391325] Thu, 12 March 2009 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Think I misread ashoka_bl's query as using NOT IN, which would need COUNT(*) > 1.
I like that last solution.

Just to be REALLY picky - these last few solutions all rely on it not being possible to have two the same records with the same team, ext_id and both have type 'N'.

OP doesn't specify keys on the table but I wouldn't be surprised if the only key was on ID.

EDIT: *Sigh* - should check facts before opening mouth.
Both JRowbottoms queries are immune to that issue. ashoka's isn't.

[Updated on: Thu, 12 March 2009 05:40]

Report message to a moderator

Re: Need help to write a SQL [message #391720 is a reply to message #391459] Fri, 13 March 2009 03:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actualy, you've got a point there - my queries won't return records if there are two rows, both of type N - I wonder if the OP will ever clarify their requirements?
Previous Topic: update data according to other column (merged 6)
Next Topic: PLSQL Generic Packge Which creates package and package body based on table name
Goto Forum:
  


Current Time: Wed Dec 07 18:22:18 CST 2016

Total time taken to generate the page: 0.07068 seconds