trx trx - 1 year ago 69
SQL Question

Oracle select with sub queries

I have two oracle select queries like

SELECT loc.location AS LOCATION , req.requisition AS REQ
FROM location_view loc, requisition_view req, association ass
WHERE loc.name = 'ABC' AND req.name = 'TRANSFER'
AND ass.entity_id_2 = req.entity_id AND ass.entity_id_1 = loc.entity_id


And the result looks like:

enter image description here

Other query is like:

SELECT req.requisition AS req, exp.experiment AS expt
FROM experiment_view exp, requisition_view req, association_view ass
WHERE expt.name = 'RETRIEVAL'AND req.name = 'TRANSFER'
AND ass.entity_id_2 = req.entity_id AND ass.entity_id_1 = expt.entity_id


Result:

enter image description here

I am trying to combine these two
SELECT
queries so I get to see these results:

enter image description here

Should I be using Sub-Queries to see the combined result or is there any other way of optimizing?

Answer Source

I'm not sure the provided solutions are correct. All of them are using 1 join to the association table. You need 2. Because Association looks to be a generic mapping table so the row that joins locations to requisitions is not the same as the one which joins requisitions to experiments. Maybe i'm wrong but i'd go for :

SELECT
  loc.location as LOCATION , 
  req.requisition as REQ, 
  exp.experiment as EXPT
FROM  location_view    loc
JOIN  association      asslr ON asslr.entity_id_1 = loc.entity_id
JOIN  requisition_view req   ON asslr.entity_id_2 = req.entity_id and req.name = 'TRANSFER'
JOIN  association_view assre ON assre.entity_id_2 = req.entity_id
JOIN  experiment_view  exp   ON assre.entity_id_1 = exp.entity_id AND exp.name = 'RETRIEVAL'
WHERE loc.name = 'ABC' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download