Kiran Bandal Kiran Bandal -4 years ago 85
SQL Question

Create view by pulling data from three tables

I know this questions has been asked several times, however it did not solve my question.

I have three tables, JD, LYR and VC ,all three have different schema. I want to create a view that will pull data from these three tables such that, it will have record that is present in JD and LYR and also in LYR and VC. So, I think there should be inner join between JD/LYR and LYR/VC. I do not want to join JD and VC.

I wrote the sql, however, if it shows the record that is present in JD and LYR, then it will not show the matching record between LYR and VC and vice versa. My current SQL is as below.

CREATE OR REPLACE VIEW TEST_VIEW AS
SELECT JD.REQ_ID,
JD.REF_NO,
LYR.OID_VAL,
VC.ACCOUNT_STATUS,
VC.ACCOUNT_NUMBER
FROM JD
INNER JOIN LYR
ON (JD.REQ_ID = LYR.REQ_ID)
INNER JOIN VC
ON LYR.REQ_ID = VC.REQUEST_ID;


I tried and changed the join types to other joins like Left outer etc, but did not work.Any help will be appreciated regarding the SQL for this view.

Thanks in advance,
KB.

Answer Source

Since your query inner-joins all three tables, it will only show a record where there is matching data in all three tables. As I understand it, you want record to be shown where there is matching data in LYR and JD or in LYR and VC (or in all three).

I'm not aware of a way to do this directly using joins, so I'd left-join them with an additional condition:

CREATE OR REPLACE VIEW TEST_VIEW AS
SELECT JD.REQ_ID,
       JD.REF_NO,
       LYR.OID_VAL,
       VCASE.ACCOUNT_STATUS,
       VCASE.ACCOUNT_NUMBER
FROM LYR
LEFT JOIN JD
    ON (LYR.REQ_ID = JD.REQ_ID)
LEFT JOIN VC
    ON (LYR.REQ_ID = VC.REQUEST_ID)
WHERE JD.REQ_ID IS NOT NULL OR VC.REQUEST_ID IS NOT NULL;

Another possible approach would be to use UNION (and not UNION ALL):

CREATE OR REPLACE VIEW TEST_VIEW AS
SELECT JD.REQ_ID,
       JD.REF_NO,
       LYR.OID_VAL,
       VCASE.ACCOUNT_STATUS,
       VCASE.ACCOUNT_NUMBER
FROM LYR
INNER JOIN JD
    ON (LYR.REQ_ID = JD.REQ_ID)
LEFT JOIN VC
    ON (LYR.REQ_ID = VC.REQUEST_ID)
UNION
SELECT JD.REQ_ID,
       JD.REF_NO,
       LYR.OID_VAL,
       VCASE.ACCOUNT_STATUS,
       VCASE.ACCOUNT_NUMBER
FROM LYR
INNER JOIN VC
    ON (LYR.REQ_ID = VC.REQUEST_ID)
LEFT JOIN JD
    ON (LYR.REQ_ID = JD.REQ_ID);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download