CODEWITHSUNDEEP
sqlselectoracle11g
Mike
Mike

Reputation: 801

Oracle - Comparing multiple records

I am stuck with a strange scenario now.

I have a table with many records. A few of which looks like:

Table 1:

----------------------------------------------
M_ID                ROLE_ID
----------------------------------------------
idA                 adVA12^~^dsa25
idA                 adsf32^~^123^~^asdf32
idA                 hdghf45
idB                 fdngfhlo43^~^
idB                 pnsdfmg23
idC                 123ghaskfdnk
idC                 hafg32^~^^~^gasdfg
----------------------------------------------

and Table 2:

-----------------------------------------------------------
ROLE_ID                     ADDR1       ADDR2       ADDR3
-----------------------------------------------------------
adVA12^~^dsa25              18          ben         street
adsf32^~^123^~^asdf32       24          naruto      park
hdghf45                     18          ben         street
fdngfhlo43^~^               40          spartan     ave
pnsdfmg23                   40          spartan     ave
123ghaskfdnk                14          southpark   ave
hafg32^~^^~^gasdfg          88          brooks      st
-----------------------------------------------------------

I have these tables linked by ROLE_ID.

My requirement is that, all the ROLE_IDs of a single M_ID in Table 1 must be compared for their address fields in Table 2. In case if the address of all the ROLE_IDs corresponding to that single M_ID is not the same in Table 2, it should be returned.

i.e., in this case, my result should be:

-----------------------------
M_ID    ROLE_ID
-----------------------------
idA     adVA12^~^dsa25
idA     adsf32^~^123^~^asdf32
idA     hdghf45
-----------------------------

the M_ID, and the corresponding ROLE_IDs.

I have no idea on how to compare multiple records.

Upvotes: 1

Views: 32

Answers (2)

zythis
zythis

Reputation: 31

Slightly different approach: 

SELECT t1.m_id, substr(sys.stragg(',' || t1.role_id),2) Roles, 
t2.ADDR1, t2.ADDR2, t2.ADDR3
FROM   t1
JOIN   t2 ON t1.role_id = t2.role_id
GROUP BY t1.m_id, t2.ADDR1, t2.ADDR2, t2.ADDR3;



"M_ID"    "ROLES"             "ADDR1"   "ADDR2" "ADDR3"
"A"       "A1,A2,A3,A5,A6,A7"   "1"       "2"     "3"
"A"       "A4"                  "4"       "2"     "3"

You could add HAVING COUNT(0) > 1 to get all with matches, or use HAVING COUNT(0) = 1 to get all instances that are only used once, or use it without a HAVING to get a summary.

I used the following test data:

CREATE TABLE TEST_ROLE (
    ID INTEGER PRIMARY KEY,
    M_ID VARCHAR2(32) NOT NULL,
    ROLE_ID VARCHAR2(256) NOT NULL
);

CREATE TABLE TEST_ROLE_ADDRESS (
    ROLE_ID VARCHAR2(256) NOT NULL,
    ADDR1 VARCHAR2(1000),
    ADDR2 VARCHAR2(1000),
    ADDR3 VARCHAR2(1000)
);

INSERT INTO TEST_ROLE VALUES(1, 'A', 'A1');
INSERT INTO TEST_ROLE VALUES(2, 'A', 'A2');
INSERT INTO TEST_ROLE VALUES(3, 'A', 'A3');
INSERT INTO TEST_ROLE VALUES(4, 'A', 'A4');
INSERT INTO TEST_ROLE VALUES(5, 'A', 'A5');
INSERT INTO TEST_ROLE VALUES(6, 'A', 'A6');
INSERT INTO TEST_ROLE VALUES(7, 'A', 'A7');

INSERT INTO TEST_ROLE_ADDRESS VALUES ('A1', '1', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A2', '1', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A3', '1', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A4', '4', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A5', '1', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A6', '1', '2', '3');
INSERT INTO TEST_ROLE_ADDRESS VALUES ('A7', '1', '2', '3');

Upvotes: 2

Mureinik
Mureinik

Reputation: 311316

I'd join the tables and count the distinct number of addresses:

SELECT m_id, role_id
FROM   (SELECT t1.m_id AS m_id,
               t1.role_id AS role_id,
               COUNT(DISTINCT t2.addr1 || '-' || t2.addr2 || '-' || t2.addr3)
                    OVER (PARTITION BY t1.m_id) AS cnt
        FROM   t1
        JOIN   t2 ON t1.role_id = t2.role_id) t
WHERE  cnt > 1

Upvotes: 3

Related Questions