Home » SQL & PL/SQL » SQL & PL/SQL » need help in sql query (9i)
need help in sql query [message #391034] Tue, 10 March 2009 13:38 Go to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
hi
i need help in the sql query.
here is the situation ....

table name = t_emp

s_id b_id
-----------
1 1
1 2
1 3
2 1
2 3
3 1
3 2
3 3
4 1


now i have written -

select b_id from t_emp where s_id = 1

o/p will be

b_id
----
1
2
3

now i need to find out the s_id where the b_id will be the (1,2,3)


so i have written the query


select t1.s_id from t_emp t1 where t1.b_id = all(select b_id from t_emp t2 where t2.s_id =1)

here t2.s_id = 1 (input s_id)

output will be

s_id
----
1
3

(as in the s_id 1 & 3 the b_id are 1,2,3)

hope i have made u understand.
please help me out

thanks
amarnath

NB- oracle version = 9i

[Updated on: Tue, 10 March 2009 13:44] by Moderator

Report message to a moderator

Re: need help in sql query [message #391035 is a reply to message #391034] Tue, 10 March 2009 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 11 November 2008 09:24
Yes, it can be done in many ways and it has been explained here many times (not so far than last week), so please search it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Here are your topics titles:
need help in sql query
need a help in SQL
sql problem
hi
help needed in sql
need help
want to solve a problem in sql urgently
need help urgently

Maybe you should think about more meaningful title.
Correction: you MUST think about it.
Re: need help in sql query [message #391087 is a reply to message #391034] Tue, 10 March 2009 22:26 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
micheal..
i will look into it...still if possible please post it once more...as the forum topics are too much, so it will be hard to search i believe. still i will look for it in the prev topis.

thanks
amarnath
Re: need help in sql query [message #391089 is a reply to message #391034] Tue, 10 March 2009 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: need help in sql query [message #391223 is a reply to message #391087] Wed, 11 March 2009 08:35 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
royamarnath wrote on Tue, 10 March 2009 23:26
micheal..
i will look into it...still if possible please post it once more...as the forum topics are too much, so it will be hard to search i believe. still i will look for it in the prev topis.



If you click on your name and then show all message by... you can see all your topics.

http://www.orafaq.com/forum/u/79920/66800/
Re: need help in sql query [message #391261 is a reply to message #391034] Wed, 11 March 2009 10:08 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
i have not asked for this thing before...
anyways...if you guys post me it will be better for me.
otherwise i will look..
thanks for your help
amarnath
Re: need help in sql query [message #391263 is a reply to message #391261] Wed, 11 March 2009 10:10 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You are not understanding the point that is being made. The list of topics posted by Michel is a list of topic titles that you have used. Each and every one of which are non descriptive. In theposting guidelines you are asked to make your subject lines descriptive. Please do so from now on.
Thanks
Re: need help in sql query [message #391264 is a reply to message #391034] Wed, 11 March 2009 10:13 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
ya ok...actually i am not a regular user of this group.
this group helped me a lot when i stuck in problem.
next time i will try to use the guidelines.
thanks
amarnath
Re: need help in sql query [message #391266 is a reply to message #391034] Wed, 11 March 2009 10:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
select s_id
from t_emp
where b_id in (select b_id 
               from t_emp 
               where s_id = 1)


Or, if you prefer joins to subqueries
select t1.s_id
from   t_emp t1
      ,t_emp t2
where  t1.b_id = t2.b_id
and    t2.s_id = 1;
Re: need help in sql query [message #391275 is a reply to message #391223] Wed, 11 March 2009 10:44 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
i had written the first approach before..but it is giving incorrect result.

select s_id
from t_emp
where b_id in (select b_id
from t_emp
where s_id = 1)

as because "in" works like "or". so here you will get the output

s_id
-----
1
2
3


but my desired output will be

s_id
-----
1
3


i need the s_id where the b_id will be same for s_id =1
for ex: s_id = 1 has b_ids 1,2,3

so only s_id 3 has the same 1,2,3 b_ids

so 1,3 are the answer.


tabledata i am again providing

s_id b_id
----------
1 1
1 2
1 3
2 1
2 3
3 1
3 2
3 3
4 3


hope this makes sense. if you still need clarification please let me know.

[Updated on: Wed, 11 March 2009 11:17] by Moderator

Report message to a moderator

Re: need help in sql query [message #391280 is a reply to message #391275] Wed, 11 March 2009 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still didn't follow the guidelines and it is useless to add 20 empty lines at the end of your post.

Regards
Michel
Re: need help in sql query [message #391283 is a reply to message #391034] Wed, 11 March 2009 11:24 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
i am sorry boss. intentionally i have not added 20 lines. anyways...i really dont know how it (20 blank lines) went. sorry..
Re: need help in sql query [message #391284 is a reply to message #391034] Wed, 11 March 2009 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The PREVIEW button works well!
Re: need help in sql query [message #391289 is a reply to message #391034] Wed, 11 March 2009 11:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is why we really like people to post test cases with tables ,insert statements and the like - it clears things up a lot.

If I understand you correctly, you want all the s_ids which have the same set of b_ids as the set returned for s_id = 1

Is this correct?
Re: need help in sql query [message #391296 is a reply to message #391289] Wed, 11 March 2009 12:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The easiest way I can think of to do this (assuming I'm right about what you want) would be to use Tom Kytes STRAGG function:
create table test_140 (s_id number, b_id number);

 insert into test_140 values (1, 1);
 insert into test_140 values (1, 2);
 insert into test_140 values (1, 3);
 insert into test_140 values (2, 1);
 insert into test_140 values (2, 3);
 insert into test_140 values (3, 1);
 insert into test_140 values (3, 2);
 insert into test_140 values (3, 3);
 insert into test_140 values (4, 3);

select s_id
      ,b_ids
from (select s_id
            ,stragg(b_id) over(partition by s_id order by b_id rows between unbounded preceding and unbounded following) b_ids
            ,row_number() over (partition by s_id order by b_id) rnum
      from test_140)
where rnum = 1
and b_ids = (select distinct stragg(b_id) over(partition by s_id order by b_id rows between unbounded preceding and unbounded following)
             from   test_140
             where  s_id = 1);
Re: need help in sql query [message #391298 is a reply to message #391283] Wed, 11 March 2009 12:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Ok Probably this
with t
as
(select 1 s_id, 1 b_id from dual union all
 select 1, 2 from dual union all
 select 1, 3 from dual union all
 select 2, 1 from dual union all
 select 2, 3 from dual union all
 select 3, 1 from dual union all
 select 3, 2 from dual union all
 select 3, 3 from dual union all
 select 4, 1 from dual
)
select s_id from t, 
(select b_id, count(*) over() cnt from t where s_id = 1) t1
where t.b_id = t1.b_id (+)
group by s_id
having 
count(distinct case when t.b_id = t1.b_id then t.b_id end) = max(t1.cnt)

In terms of the performance I am not sure how well it be but I will leave that exercise for you to solve.

Regards

Raj

[Edit: ] Added distinct condition in the count function

[Updated on: Wed, 11 March 2009 12:47]

Report message to a moderator

Re: need help in sql query [message #391601 is a reply to message #391034] Thu, 12 March 2009 12:15 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
hello
rajaram & rowbottom

i will look into both of the solution. as i dont have oracle installed in my machine so i cant check it now.

but i feel that query will be giving perfect result.
anyways...thanks very much for your help.

i had tried this query using ALL. but it was not giving result.

select s_id from t_emp t1 where t1.b_id = ALL(select t2.b_id from t_emp t2 where t2.s_id = 1 )

t.s_id = 1 (input)

but this query is not working.

anyways...i have got the solution hopefully. i will check and let u know.
amarnath
Re: need help in sql query [message #391670 is a reply to message #391034] Thu, 12 March 2009 23:07 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member

I have checked the last two solutions in my office machine. (oracle version 9i).
last solution works fine. but the solution what Jrowbottom has provided, unfortunately it wont worked. stragg() function is undefined. Invalid Identifier stragg. I am not sure whether this function is availble in oracle 10g or not.

anyways...thanks to all...that query was driving me nuts.huh!
Re: need help in sql query [message #391682 is a reply to message #391670] Fri, 13 March 2009 01:01 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
royamarnath wrote on Fri, 13 March 2009 05:07

I have checked the last two solutions in my office machine. (oracle version 9i).
last solution works fine. but the solution what Jrowbottom has provided, unfortunately it wont worked. stragg() function is undefined. Invalid Identifier stragg. I am not sure whether this function is availble in oracle 10g or not.


Quote:
The easiest way I can think of to do this (assuming I'm right about what you want) would be to use Tom Kytes STRAGG function:


Search for it. It is not a standard function, but it i a much used function provided by Tom Kyte
Previous Topic: How to Display N row for each person ID?
Next Topic: update data according to other column (merged 6)
Goto Forum:
  


Current Time: Fri Dec 09 13:44:10 CST 2016

Total time taken to generate the page: 0.17723 seconds