Home » SQL & PL/SQL » SQL & PL/SQL » Help needed for SQL Query (merged)
| Help needed for SQL Query (merged) [message #393142] |
Fri, 20 March 2009 09:05  |
sankaram
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
Hi All,
I have data like this in the table
C1 C2 GROUP
1 2 100
1 3 100
2 4 100
2 5 200
5 10 200
3 11 300
I need output as follows
C1 C2
1 2
1 3
1 4 (This is not required in the output as this relation in
the same group 100)
2 4
1 5 (1->2, 2->5, so 1->5 Required because 2,5 is in 200 group)
1 10
2 5
2 10(This is not required in the output as this relation in
the same group 200)
3 11
Can you suggest me how to get this through query
|
|
|
|
| Re: Help needed for SQL Query (merged) [message #393144 is a reply to message #393142] |
Fri, 20 March 2009 09:11  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Explain with words what are the requirements.
Post a Test case: create table and insert statements along with the result you want with these data.
Regards
Michel
[Updated on: Fri, 20 March 2009 09:11] Report message to a moderator |
|
|
|
| Re: Help needed for SQL Query (merged) [message #393158 is a reply to message #393142] |
Fri, 20 March 2009 10:15 |
sankaram
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
CREATE TABLE TESTING
(C1 NUMBER,
C2 NUMBER,
GRP NUMBER)
/
INSERT INTO TESTING VALUES (1,2,100);
INSERT INTO TESTING VALUES (1,3,100);
INSERT INTO TESTING VALUES (2,4,100);
INSERT INTO TESTING VALUES (2,5,200);
INSERT INTO TESTING VALUES (5,10,200);
INSERT INTO TESTING VALUES (3,11,200);
I want output as follows
C1 C2
1 2
1 3
1 5
1 10
2 4
2 5
3 11
5 10
Logic is C2 is output of C1, For a give Grp value lest say 100, check the output of C1(ie C2) is input to in other groups (other than 100, i.e 200)
If yes then we need to display the result in the Output
For example
C1 C2 GRP
1 2 100
2 4 100
2 5 200
5 10 200
Output should be
1 2
1 5
1 10
2 4
2 5
5 10
I hope I have explained you.
[Updated on: Fri, 20 March 2009 11:18] by Moderator Report message to a moderator |
|
|
|
| Re: Help needed for SQL Query (merged) [message #393166 is a reply to message #393158] |
Fri, 20 March 2009 11:20 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You didn't format and align the column results, see how it is easy just adding the code tags (read guide).
You didn't tell us what is your Oracle version.
Have a look at CONNECT BY and hierarchial queries in SQL Reference.
And should be the result for the test case you gave?
Regards
Michel
[Updated on: Fri, 20 March 2009 11:29] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Help needed for SQL Query (merged) [message #393520 is a reply to message #393490] |
Mon, 23 March 2009 03:58 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> select c1, c2
2 from ( select to_number(substr(sys_connect_by_path(c1,'/'),
3 2, instr(sys_connect_by_path(c1,'/')||'/','/',1,2)-2))
4 c1,
5 grp,
6 to_number(substr(sys_connect_by_path(grp,'/'),
7 2, instr(sys_connect_by_path(grp,'/')||'/','/',1,2)-2))
8 root_grp,
9 c2,
10 level lvl
11 from testing
12 connect by c1 = prior c2
13 )
14 where grp != root_grp or lvl = 1
15 /
C1 C2
---------- ----------
1 2
1 5
1 10
2 4
2 5
5 10
6 rows selected.
Regards
Michel
|
|
|
|
| Re: Help needed for SQL Query (merged) [message #393597 is a reply to message #393520] |
Mon, 23 March 2009 09:24 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Another answer is:
SQL> select c1, c2
2 from ( select to_number(substr(sys_connect_by_path(c1,'/'),
3 2, instr(sys_connect_by_path(c1,'/')||'/','/',1,2)-2))
4 c1,
5 substr(sys_connect_by_path(grp,'/')||'/',
6 1, instr(sys_connect_by_path(grp,'/')||'/','/',1,2))
7 root_grp,
8 substr(sys_connect_by_path(grp,'/')||'/',
9 instr(sys_connect_by_path(grp,'/')||'/','/',1,2),
10 instr(sys_connect_by_path(grp,'/')||'/','/',1,3))
11 grp_path,
12 c2
13 from testing
14 connect by c1 = prior c2
15 )
16 where instr(nvl(grp_path,'/'),root_grp) = 0
17 /
C1 C2
---------- ----------
1 2
1 5
1 10
2 4
2 5
5 10
6 rows selected.
There is a difference between these 2 queries, I let you find it. They don't answer to the exactly same question.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Tue Nov 11 05:37:25 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
