oracle-sqldeveloper

How to Compare the data and set a variable


CREATE TABLE registration 
(
    Id INT NOT NULL AUTO_INCREMENT,
    CONT_ID NUMBER(10,0); 
    COMM_ID NUMBER(10,0);(Can have null values Nullable)
    PRIMARY KEY(Id)
);

CREATE TABLE Contact 
(
    Id INT NOT NULL AUTO_INCREMENT,
   FirstName CHAR(30 BYTE)
   LastName CHAR(30 BYTE)
);

CREATE TABLE Communication 
(
    Id INT NOT NULL AUTO_INCREMENT,
   FirstName CHAR(30 BYTE)
   LastName CHAR(30 BYTE)
);

I have a registration table with RegistratonId, Contact_Id, CommunicationId, Remark.

And a Contact table

ContactID   FirstName   LastName

And a Communication table

CommunicationId   FirstName   LastName

I need to derive a condition like For a particular registration ID if the Firstname and LastName of Contact table is same as the Firstname and LastName of Communication table set a variable as ismatching. If comm id is null by ismatching can be set to true


Solution

  • You can use EXISTS in a CASE expression:

    SELECT r.*,
           CASE
           WHEN EXISTS(
             SELECT 1
             FROM   contact t
                    INNER JOIN communication m
                    ON (   t.firstname = m.firstname
                       AND t.lastname  = m.lastname)
             WHERE  t.id = r.cont_id
             AND    m.id = r.comm_id
           )
           THEN 'true'
           ELSE 'false'
           END AS ismatching
    FROM   registration r
    

    Which, if you have the sample data (correcting your DDL statements for invalid syntax and assuming that you are using Oracle 12 or later to support IDENTITY columns):

    CREATE TABLE Contact 
    (
      Id        NUMBER(10,0)
                GENERATED ALWAYS AS IDENTITY
                NOT NULL
                PRIMARY KEY,
      FirstName VARCHAR2(30 BYTE),
      LastName  VARCHAR2(30 BYTE)
    );
    
    CREATE TABLE Communication 
    (
      Id        NUMBER(10,0)
                GENERATED ALWAYS AS IDENTITY
                NOT NULL
                PRIMARY KEY,
      FirstName VARCHAR2(30 BYTE),
      LastName  VARCHAR2(30 BYTE)
    );
    
    CREATE TABLE registration 
    (
      Id      NUMBER(10,0)
              GENERATED ALWAYS AS IDENTITY
              NOT NULL
              PRIMARY KEY,
      CONT_ID REFERENCES contact, 
      COMM_ID REFERENCES communication
    );
    
    INSERT INTO contact (FirstName, LastName)
    SELECT 'Alice', 'Abbot' FROM DUAL UNION ALL
    SELECT 'Beryl', 'Baron' FROM DUAL UNION ALL
    SELECT 'Carol', 'Count' FROM DUAL UNION ALL
    SELECT 'Debra', 'Duke'  FROM DUAL;
    
    INSERT INTO communication (FirstName, LastName)
    SELECT 'Carol', 'Count' FROM DUAL UNION ALL
    SELECT 'Beryl', 'Baron' FROM DUAL UNION ALL
    SELECT 'Alice', 'Abbot' FROM DUAL UNION ALL
    SELECT 'Emily', 'Earl'  FROM DUAL;
    
    INSERT INTO registration (cont_id, comm_id)
    SELECT 1, 3 FROM DUAL UNION ALL
    SELECT 1, 1 FROM DUAL UNION ALL
    SELECT 2, 2 FROM DUAL UNION ALL
    SELECT 3, 1 FROM DUAL UNION ALL
    SELECT 4, 4 FROM DUAL;
    

    Then the output is:

    ID CONT_ID COMM_ID ISMATCHING
    1 1 3 true
    2 1 1 false
    3 2 2 true
    4 3 1 true
    5 4 4 false

    If you want to display the names as well then use LEFT OUTER JOIN and a CASE expression:

    SELECT r.id,
           r.cont_id,
           t.firstname,
           t.lastname,
           r.comm_id,
           m.firstname,
           m.lastname,
           CASE
           WHEN t.firstname = m.firstname
           AND  t.lastname  = m.lastname
           THEN 'true'
           ELSE 'false'
           END AS ismatching
    FROM   registration r
           LEFT OUTER JOIN contact t
           ON t.id = r.cont_id
           LEFT OUTER JOIN communication m
           ON m.id = r.comm_id;
    

    Which outputs:

    ID CONT_ID FIRSTNAME LASTNAME COMM_ID FIRSTNAME LASTNAME ISMATCHING
    2 1 Alice Abbot 1 Carol Count false
    4 3 Carol Count 1 Carol Count true
    3 2 Beryl Baron 2 Beryl Baron true
    1 1 Alice Abbot 3 Alice Abbot true
    5 4 Debra Duke 4 Emily Earl false

    fiddle