Home » SQL & PL/SQL » SQL & PL/SQL » query help need, PLEASE
query help need, PLEASE [message #244093] Mon, 11 June 2007 10:36 Go to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member
Hi ,

Please help me to write query. Table 'test' contains the following data.

group_name group_members
group1 SAM
group1 BEN
group1 JOTY
group1 BTTTY
group1 CHRIS
group2 ALLY
group2 JOHN
group2 MOLLY
group2 BETTY
group2 CHRIS
group3 STEVE
group3 SIVA
group3 JOTY
group3 BETTY
group3 DAVID
group4 ANDY
group4 RAM
group4 JOTY
group4 BETTY
group4 CHRIS

Basically I want to extract only those 'group_name's that has at least the following group_members

JOTY, BETTY , CHRIS.

Output should select rows with group_name = group1 and group4

Any help is very much appreciated.

Thanks,
Ajit
icon7.gif  Re: query help need, PLEASE [message #244095 is a reply to message #244093] Mon, 11 June 2007 10:39 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
What about

SELECT GROUP_NAME FROM TEST
WHERE GROUP_MEMBERS IN ('JOTY','BETTY','CHRIS')

or

SELECT * FROM TEST
WHERE GROUP_MEMBERS IN ('JOTY','BETTY','CHRIS')

[Updated on: Mon, 11 June 2007 10:40]

Report message to a moderator

Re: query help need, PLEASE [message #244097 is a reply to message #244093] Mon, 11 June 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.

Regards
Michel
Re: query help need, PLEASE [message #244098 is a reply to message #244093] Mon, 11 June 2007 10:43 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

the query should be :

select * from test 
where group_name in('JOTY','BETTY','CHRIS')
and group_members in('group1','group4');
Re: query help need, PLEASE [message #244099 is a reply to message #244095] Mon, 11 June 2007 10:45 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
At second glance to your question though, why are you looking to get only group 1 and 4 when group2 and group3 would also contain the members you're looking for as well??????

Re: query help need, PLEASE [message #244101 is a reply to message #244095] Mon, 11 June 2007 10:46 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member
Hi Mivey4,

This would select all 4 group_names. But group2 and group3 does not contain all the 3 names ('JOTY','BETTY','CHRIS')

Thanks,
Ajit
Re: query help need, PLEASE [message #244104 is a reply to message #244099] Mon, 11 June 2007 10:48 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member

I am looking only those groups that has ALL the 3 groups ( and may be more)
Re: query help need, PLEASE [message #244107 is a reply to message #244104] Mon, 11 June 2007 10:50 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member

Sorry, I should say:

I am looking for only those groups that has ALL the 3 members in it ( and may be more).
Re: query help need, PLEASE [message #244110 is a reply to message #244093] Mon, 11 June 2007 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please always post your Oracle version (4 decimals).
Please read and apply How to format your posts.

What did you already try? Post it.

Regards
Michel
Re: query help need, PLEASE [message #244111 is a reply to message #244098] Mon, 11 June 2007 10:59 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member
Hi Sanka_Yanka,

We can not use "where group_name in (group1,group4)"

That is what we are looking for.

Thanks,
Ajit

Re: query help need, PLEASE [message #244115 is a reply to message #244110] Mon, 11 June 2007 11:17 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member
Hi Michel,

I am using oracle version 10.

I tried the following query which is same as suggested by Mivey4

SELECT GROUP_NAME FROM TEST
WHERE GROUP_MEMBERS IN ('JOTY','BETTY','CHRIS')

But this query would select all groups, but we need only those groups that has ALL the 3 group_names in them.

Regards,
Ajit
Re: query help need, PLEASE [message #244117 is a reply to message #244115] Mon, 11 June 2007 11:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If it is certain that there are not duplicates group <-> member entries this could work :

( Un-Tested, since I don't have the table or the data )

SELECT group_name FROM (
    SELECT group_name, 
           Count(*) cnt,
           FROM test
     WHERE GROUP_MEMBERS IN ('JOTY','BETTY','CHRIS')
) WHERE cnt = 3;


But I have the nagging feeling that there should be a better way. Wink
Re: query help need, PLEASE [message #244128 is a reply to message #244117] Mon, 11 June 2007 12:26 Go to previous messageGo to next message
bajwaaj
Messages: 7
Registered: December 2005
Location: Toronto
Junior Member
Hi Thomas,

It works so well. Thanks a million.

Thanks,
Ajit
Re: query help need, PLEASE [message #244148 is a reply to message #244128] Mon, 11 June 2007 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need the subquery, try to use HAVING clause.

Regards
Michel
Re: query help need, PLEASE [message #244173 is a reply to message #244093] Mon, 11 June 2007 16:48 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Try the following

select group_name
from test a, test b, test c
where a.group_name = b.group_name
and a.group_name = c.group_name
and a.group_members = 'JOTY'
and b.group_members = 'BETTY'
and c.group_members = 'CHRIS'
group by group_name;
Re: query help need, PLEASE [message #244202 is a reply to message #244173] Mon, 11 June 2007 23:15 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Interesting.
I wonder which one is better, grouping or joining, assuming you have an [unique] index on group_member[, group_name].

Regards
Michel
Previous Topic: Regarding bulk update
Next Topic: v$session_longops
Goto Forum:
  


Current Time: Sun Dec 11 04:40:03 CST 2016

Total time taken to generate the page: 0.13329 seconds