Selecting Matching values from three different table and combining them in one table in Oracle

I have Four tables as follows

Review (REV_ID pk , REV_NAME)

Meeting (MEETING_ID pk,MEETING_NAME,REV_ID fk to Review)

Task (TASK_ID pk,TASK_NAME,REV_ID fk to Review)

Answer (ANS_ID pk,ANS_NAME,REV_ID fk to Review)


Now I want to select a particular Review and want to create a table with

Linked meetings

Linked answers

Linked tasks

How shall I proceed with it?

I tried writing join query but I was only able to get data if Rev_ID is present in all tables?

select * from
(SELECT *
FROM meeting
WHERE EXISTS (SELECT *
FROM review WHERE meeting.rev_id
=review.rev_id)
and meeting.rev_id=142),
(SELECT *
FROM answer
WHERE EXISTS (SELECT *
FROM review WHERE answer.rev_id
=rev.rev_id)
and answer.ans_rev_id=142),

(SELECT *
FROM task
WHERE EXISTS (SELECT *
FROM review WHERE task.rev_id
=review.rev_id)
and task.rev_id=142) r;


Note : Here I tried static Rev_ID =142 to check data.

From above query i am getting output only if data exist in all four table, But if Data does not exist in any table, It does not return remaining value.

I want at-least names of all table's in final output.

1 Answer
1

Try the following, let us know if this meets your requirements.

SELECT rv.rev_id,
rv.rev_name,
mt.meeting_name,
tk.task_name,
ans.ans_name
FROM review rv
LEFT OUTER JOIN meeting mt ON (rv.rev_id = mt.rev_id)
LEFT OUTER JOIN task tk ON (rv.rev_id = tk.rev_id)
LEFT OUTER JOIN answer ans ON (rv.rev_id = ans.rev_id)
WHERE rv.rev_id = 142


SQL Fiddle Demo

If the above SQL is fine, prefix it with create table or view syntax to combine them into one.

LEFT OUTER JOIN

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.