Home » SQL & PL/SQL » SQL & PL/SQL » Common Rows
Common Rows [message #243575] Thu, 07 June 2007 15:22 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
All,
I have a table in which I store access level's for users. I'm trying to see how I can retrieve common rows (basically other users with the same access level) when I pass in a U_ID

Data looks like this:

ID U_ID A_LEVEL DIV
--- ---- --------- ---
1 1 Level1 10
2 1 Level2 20
3 1 Level1 20
4 1 Level2 10
5 1 Level3 30
6 2 Level1 10
7 2 Level2 20
8 2 Level3 30
9 3 Level3 30
10 4 Level4 10
11 5 Level5 50


Rows returned should be like following if I pass in U_ID of 1

ID U_ID A_LEVEL DIV
--- ---- --------- ---
6 2 Level1 10
7 2 Level2 20
8 2 Level3 30
9 3 Level3 30


I'd appreciate any help in writing this sql.

DDL's to create the table and data:

create table test1(id integer, u_id integer, a_level varchar2(20), div varchar2(20));

INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(1,1,'Level1','10')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(2,1,'Level2','20')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(3,1,'Level1','20')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(4,1,'Level2','10')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(5,1,'Level3','30')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(6,2,'Level1','10')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(7,2,'Level2','20')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(8,2,'Level3','30')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(9,3,'Level3','30')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(10,4,'Level4','10')
/
INSERT INTO test1
(ID,U_ID,A_LEVEL,DIV)
VALUES
(11,5,'Level5','50')
/


Re: Common Rows [message #243577 is a reply to message #243575] Thu, 07 June 2007 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.

SQL> select *
  2  from test1
  3  where a_level in (select a_level from test1 where u_id=1)
  4    and u_id != 1
  5  /
        ID       U_ID A_LEVEL              DIV
---------- ---------- -------------------- --------------------
         6          2 Level1               10
         7          2 Level2               20
         9          3 Level3               30
         8          2 Level3               30

4 rows selected.

Regards
Michel
Re: Common Rows [message #243579 is a reply to message #243577] Thu, 07 June 2007 15:33 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Michel,
Thanks for you prompt response. Actually, I need to check both a_level and div to make sure they both match and not just a_level.

select *
from test1
where a_level in (select a_level from test1 where u_id=1)
and div in (select div from test1 where u_id=1)
and u_id != 1
/

Would this be it?

[Updated on: Thu, 07 June 2007 15:34]

Report message to a moderator

Re: Common Rows [message #243604 is a reply to message #243579] Thu, 07 June 2007 23:06 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know, maybe, you know the specifications, I just followed what you posted:
Quote:
I'm trying to see how I can retrieve common rows (basically other users with the same access level) when I pass in a U_ID

If now it is:
Quote:
I need to check both a_level and div to make sure they both match

change "a_level in (select a_level from test1 where u_id=1)" to "(a_level,div) in (select a_level, div from test1 where u_id=1)".
The query you posted means "a_level and div must match one the u_id=1 but both values do not need to be from the same u_id=1 row".
Only you know which is the actual need.

Regards
Michel

Previous Topic: SQL tuning document or hints if available ...
Next Topic: Hi Experts,
Goto Forum:
  


Current Time: Fri Dec 09 07:59:35 CST 2016

Total time taken to generate the page: 0.12691 seconds