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
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
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'