CODEWITHSUNDEEP
sqldatabaseoracle-databasesubquery
Michael Melville
Michael Melville

Reputation: 3

Oracle SQL query and perhaps subqueries across 4 tables

I have 4 tables and need to get values from 2 of them.

policy policy_id, template_id

quote quote_id, policy_id

quote_clause quote_clause_id, quote_id

quote_clause_variable quote_clause_variable_id, quote_clause_id, variable_name, value

-

I'm trying to get all the policy_id's that have a specific template_id of 28 and from there, get the quote_clause_variable value where the variable name is 'R01011C1'.

I'm a little lost at this point, neither of the queries below are anywhere near correct.

SELECT policy.policy_id, quote_clause_variable.value 
FROM mciaweb.policy 
LEFT JOIN mciaweb.quote ON policy_id = policy_id 
LEFT JOIN mciaweb.quote_clause ON quote_id = quote_id 
LEFT JOIN mciaweb.quote_clause_variable ON quote_clause_id = quote_clause_id 
WHERE policy.template_id = '28' 
AND variable_name = 'R01011C1';


select 
(select value from mciaweb.quote_clause_variable 
where variable_name in ('R01011C1') 
and quote_clause_id in 
(select quote_clause_id from MCIAWEB.quote_clause where quote_id in 
(select quote_id from MCIAWEB.quote where policy_id in 
(select policy_id from mciaweb.policy where template_id in ('28') 
)))) situation, 
policy_id  
from mciaweb.policy where template_id = '28'
;

Many thanks in advance for any help with this.

Upvotes: 0

Views: 90

Answers (1)

Holger Brandt
Holger Brandt

Reputation: 4354

Your first query is pretty close, if I understand what you are looking for. Here is what I tried:

SELECT p.policy_id, qcv.value 
FROM policy p
INNER JOIN quote q ON p.policy_id = q.policy_id 
INNER JOIN quote_clause qc ON qc.quote_id = q.quote_id 
INNER JOIN quote_clause_variable qcv ON qcv.quote_clause_id = qc.quote_clause_id 
WHERE p.template_id = '28' 
AND qcv.variable_name = 'R01011C1';

Upvotes: 1

Related Questions