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
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 |