Hi Connor/Chris,
Can you pleas help to build a query based on below inputs and condition:
table structe is shared in liveSQL as well.
DROP TABLE TEST_LOGIN_MASTER;
DROP TABLE TEST_LOGIN_REQUEST;
DROP TABLE TEST_LOGIN_ACCESS;
CREATE TABLE TEST_LOGIN_MASTER
( PK_TEST_LOGIN_MASTER NUMBER(10,0),
LOGIN_MASTER NUMBER(10,0),
COMPANY_DETAILS NUMBER(10,0),
FK_TEST_LOGIN_REQUEST VARCHAR2(20)
);
INSERT INTO TEST_LOGIN_MASTER
SELECT 1,1015918, 1557, 'REQUEST 1' FROM DUAL UNION
SELECT 2,1015918, 1680, 'REQUEST 3' FROM DUAL UNION
SELECT 3,1015918, 13770, 'REQUEST 4' FROM DUAL UNION
SELECT 4,1015918, 13771, NULL FROM DUAL UNION
SELECT 5,1015918, 13906, NULL FROM DUAL;
CREATE TABLE TEST_LOGIN_REQUEST
( PK_TEST_LOGIN_REQUEST VARCHAR2(20),
STATUS VARCHAR2(2)
);
INSERT INTO TEST_LOGIN_REQUEST
SELECT 'REQUEST 1', 'AC' FROM DUAL UNION
SELECT 'REQUEST 2', 'AC' FROM DUAL UNION
SELECT 'REQUEST 3', 'AC' FROM DUAL;
CREATE TABLE TEST_LOGIN_ACCESS
( LOGIN_MASTER NUMBER(10,0),
COMPANY_DETAILS NUMBER(10,0)
);
INSERT INTO TEST_LOGIN_ACCESS
SELECT 1015918, 1557 FROM DUAL UNION
SELECT 1015918, 1680 FROM DUAL UNION
SELECT 1015918, 13770 FROM DUAL UNION
SELECT 1015918, 13771 FROM DUAL UNION
SELECT 1015918, 13906 FROM DUAL;
1. CHECK FK_TEST_LOGIN_REQUEST IN TEST_LOGIN_MASTER WITH LOGIN_MASTER AND COMPANY_DETAILS IF IT HAS A VALUE OR NOT
2. IF FK_TEST_LOGIN_REQUEST IS NOT NULL THEN CHECK THE STATUS IF IT IS 'AC' IN TEST_LOGIN_REQUEST
3. IF FK_TEST_LOGIN_REQUEST IS NULL THEN CHECK IN TEST_LOGIN_ACCESS WITH LOGIN_MASTER AND COMPANY_DETAILS IF THERE IS AN ENTRY
in the end i need the output like below:
1015918 1557 Y
1015918 1680 Y
1015918 13770 N
1015918 13771 Y
1015918 13906 Y
So if test_login_master.fk_test_login_request is not null you want to display Y if there's a row with test_login_request.status = 'AC'?
And if test_login_master.fk_test_login_request is null, you want to display Y if there's a match row in test_login_access on login_master and company_details?
Otherwise show N?
If so you can get what you want by:
- Outer joining request and access to master on the appropriate columns
- Using a case expression with when clauses testing your criteria and returning the appropriate value
For example:
select m.*,
case
when m.fk_test_login_request is not null and
r.status = 'AC' then 'Y'
when m.fk_test_login_request is null and
a.login_master is not null then 'Y'
else 'N'
end yn
from test_login_master m
left join test_login_request r
on m.fk_test_login_request = r.pk_test_login_request
left join test_login_access a
on a.login_master = m.login_master
and a.company_details = m.company_details ;
PK_TEST_LOGIN_MASTER LOGIN_MASTER COMPANY_DETAILS FK_TEST_LOGIN_REQUES Y
-------------------- ------------ --------------- -------------------- -
1 1015918 1557 REQUEST 1 Y
2 1015918 1680 REQUEST 3 Y
3 1015918 13770 REQUEST 4 N
4 1015918 13771 Y
5 1015918 13906 YPS - please DON'T SHOUT!