Home » SQL & PL/SQL » SQL & PL/SQL » Pls help -Multiple sub units with the same name
Pls help -Multiple sub units with the same name [message #288751] Tue, 18 December 2007 10:18 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
I have a table called sub_units.This is having the fields subid(Primary key),name,main id(foreign key) and a few other fields.
In this table, same main id has many sub ids.
The query is regarding the field 'Name'-Name could be different or same, for the main id
I want to form a query to find out the following:-
List all the multiple sub ids for a main id where all the names are the same
Only if all the names coming as same, I want to list those sub ids
If even one name is different,I don't want to list it.
thanks much,
aviana

[Updated on: Tue, 18 December 2007 10:21]

Report message to a moderator

Re: Pls help -Multiple sub units with the same name [message #288754 is a reply to message #288751] Tue, 18 December 2007 10:24 Go to previous messageGo to next message
verd
Messages: 17
Registered: December 2007
Junior Member
okay all i know is that Select * from {insert table name here} gives you everythign that table is holding.
Re: Pls help -Multiple sub units with the same name [message #288757 is a reply to message #288754] Tue, 18 December 2007 10:28 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
To verd:
If you dont know, try to learn things!
Please dont post a reply if you dont know the right answer.
Re: Pls help -Multiple sub units with the same name [message #288758 is a reply to message #288757] Tue, 18 December 2007 10:33 Go to previous messageGo to next message
verd
Messages: 17
Registered: December 2007
Junior Member
aviana wrote on Tue, 18 December 2007 10:28

To verd:
If you dont know, try to learn things!
Please dont post a reply if you dont know the right answer.




jeeez sorry for breathing! was only trying to help!
Re: Pls help -Multiple sub units with the same name [message #288759 is a reply to message #288757] Tue, 18 December 2007 10:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
create table test (sub_id number, name varchar2(10), main_id number);

insert into test values (1,'abc',1);
insert into test values (2,'abc',1);
insert into test values (3,'abc',1);
insert into test values (4,'efg',2);
insert into test values (5,'efg',2);
insert into test values (6,'ijk',3);
insert into test values (7,'lmn',3);
insert into test values (8,'opq',3);
insert into test values (9,'rst',4);


SQL> select * from test;

    SUB_ID NAME          MAIN_ID
---------- ---------- ----------
         1 abc                 1
         2 abc                 1
         3 abc                 1
         4 efg                 2
         5 efg                 2
         6 ijk                 3
         7 lmn                 3
         8 opq                 3
         9 rst                 4

9 rows selected.

  1  select sub_id, main_id, name from
  2  (
  3  select sub_id, main_id, name, count(*) over(partition by main_id) count_main_id,
  4  count(*) over(partition by main_id, name) count_main_name_id
  5  from test
  6  )
  7* where count_main_id = count_main_name_id
SQL> /

    SUB_ID    MAIN_ID NAME
---------- ---------- ----------
         1          1 abc
         2          1 abc
         3          1 abc
         4          2 efg
         5          2 efg
         9          4 rst

6 rows selected.


  1  select * from test
  2  where main_id in (
  3  select main_id from test
  4  group by main_id
  5  having count(distinct name) = 1
  6* )
SQL> /

    SUB_ID NAME          MAIN_ID
---------- ---------- ----------
         1 abc                 1
         2 abc                 1
         3 abc                 1
         4 efg                 2
         5 efg                 2
         9 rst                 4

6 rows selected.



HTH

Regards

Raj
Re: Pls help -Multiple sub units with the same name [message #288761 is a reply to message #288758] Tue, 18 December 2007 10:43 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
verd,I didn't know this is the way you help someone.
How are you going to help someone by typing something, which is not having the distant relation to the question?

I hope somebody else can help on this??
Re: Pls help -Multiple sub units with the same name [message #288795 is a reply to message #288761] Tue, 18 December 2007 14:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
aviana wrote on Tue, 18 December 2007 17:43

verd,I didn't know this is the way you help someone.
How are you going to help someone by typing something, which is not having the distant relation to the question?

I hope somebody else can help on this??

You know, you could also ignore that reply and spend your time finding the answer instead of going through the trouble of putting down somebody who tried to help you.
I admit, the help wasn't great but your first reply should have been clear enough.
There is this little "ignore" button on your screen. If you're on Windows, it is the little cross in the top-right corner; on some other os-es it's in the top-left corner
Re: Pls help -Multiple sub units with the same name [message #288992 is a reply to message #288795] Wed, 19 December 2007 03:59 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Thanks a lot Rajaram, for your help -It gave light to my brain!
I was upto my eyes yesterday and your reply was a real help..
Using 'partition' clause was new to me but thanks for putting that..second solution was familiar only thing it was not coming to mind on time..
A word of sorry to verd - Dear friend, please ignore my response to your reply.. I was thinking it must be a solution when I saw as '1 reply' but then it was not..anyway, I apologize for being harsh and sure,I wont ignore you!
Re: Pls help -Multiple sub units with the same name [message #289057 is a reply to message #288992] Wed, 19 December 2007 06:49 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi ,
In the same query, I want to exclude those rows if
1)the name field is null
2)if there is only 1 sub id for a main id (in your example,
sub id=9 and main id=4)
How can i modify the second query given by Raj to satisfy these conditions?
Thanks ain advance.

[Updated on: Wed, 19 December 2007 06:50]

Report message to a moderator

Re: Pls help -Multiple sub units with the same name [message #289058 is a reply to message #288992] Wed, 19 December 2007 06:51 Go to previous messageGo to next message
verd
Messages: 17
Registered: December 2007
Junior Member
aviana wrote on Wed, 19 December 2007 03:59

Thanks a lot Rajaram, for your help -It gave light to my brain!
I was upto my eyes yesterday and your reply was a real help..
Using 'partition' clause was new to me but thanks for putting that..second solution was familiar only thing it was not coming to mind on time..
A word of sorry to verd - Dear friend, please ignore my response to your reply.. I was thinking it must be a solution when I saw as '1 reply' but then it was not..anyway, I apologize for being harsh and sure,I wont ignore you!



okay glad we clarified that. sorry for sounding thick.
Re: Pls help -Multiple sub units with the same name [message #289068 is a reply to message #289057] Wed, 19 December 2007 07:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
give us an example. Also what have you tried.

Regards

Raj
Re: Pls help -Multiple sub units with the same name [message #289079 is a reply to message #289068] Wed, 19 December 2007 08:24 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I got the solution right..Thanks a lot for your help and quick replies.
Re: Pls help -Multiple sub units with the same name [message #289081 is a reply to message #289079] Wed, 19 December 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I got the solution right..

And you don't want to share it?

Regards
Michel
Re: Pls help -Multiple sub units with the same name [message #289083 is a reply to message #289081] Wed, 19 December 2007 08:41 Go to previous message
aviana
Messages: 101
Registered: July 2007
Senior Member
oh sure michel:)
I modified the query to solve my problem as below:-(I am appending to the query which raj has sent me, as an example here)

select * from test
  2  where main_id in (
  3  select main_id from test
  4  group by main_id
  5  having count(distinct nvl(name,'NULL')) = 1
     and count(main_id) >1
  6* )




Sorry I don't have any output here as I didnt create the table 'test' and actually my table names all are different..Anyway it worked fine.

[Updated on: Wed, 19 December 2007 08:42]

Report message to a moderator

Previous Topic: Using variable to Update columns in a loop
Next Topic: Dollar Formatting in PL/SQL
Goto Forum:
  


Current Time: Fri Dec 02 16:45:41 CST 2016

Total time taken to generate the page: 0.23567 seconds