-2

I am trying to use join in the Oracle query but I am not sure how can return all records with or without that join condition. Below is the query

Select req.barcode,contact.barcode,inst.barcode from entity_view req
join entity_association_view assreq ON assreq.entity_id_1 = req.entity_id 
join entity_view contact on assreq.entity_id_2 = contact.entity_id and contact.entity_type_name = 'CONTACT'
join entity_association_view assins ON assins.entity_id_2 = contact.entity_id 
join entity_view inst on assins.entity_id_1 = inst.entity_id and inst.entity_type_name = 'INSTITUTION'
where req.entity_type_name = 'REQUEST'

Here it returns Requests which has only the contacts. But I want the Requests that without contacts as well. I am not familiar with sub queries should I use that here to get all the records with or without contact

I used left join as well but it is bringing the records like 

Select distinct req.barcode,contact.barcode,inst.barcode from entity_view req
left join entity_association_view assreq ON assreq.entity_id_1 = req.entity_id 
left join entity_view contact on assreq.entity_id_2 = contact.entity_id and contact.entity_type_name = 'CONTACT'
left join entity_association_view assins ON assins.entity_id_2 = contact.entity_id 
left join entity_view inst on assins.entity_id_1 = inst.entity_id and inst.entity_type_name = 'INSTITUTION'
where req.entity_type_name = 'REQUEST' and req.barcode = 'GET0127' order by req.barcode

enter image description here

xyz
  • 531
  • 1
  • 10
  • 31
  • Try `LEFT JOIN` – Aaron Dietz Feb 12 '18 at 19:17
  • Change all the `join`s to `left join`s. – Gordon Linoff Feb 12 '18 at 19:17
  • Looks like a request without a contact to me – paparazzo Feb 12 '18 at 19:59
  • @GordonLinoff I tried left join already but it brings the records like I put in the screenshots. It should be only having GET0127 CON001 INS002 – xyz Feb 12 '18 at 20:00
  • @Paparazzi I am not able to get you sorry – xyz Feb 12 '18 at 20:00
  • It looks like it's working with the `LEFT JOIN` to me. `LEFT JOIN` will keep all rows from the original table, even if they don't have matching records in the table being joined to. If they don't have records in the joined table, the columns will be populated with `NULL`. I think you need to add some sample data and show what you expect to get compared to what you are currently getting, otherwise we can't understand what you want. – Aaron Dietz Feb 12 '18 at 20:36

1 Answers1

0

This is up to you as how you would like to analyze your data.

A query such as what you are doing can be done without any subqueries. While I was an Software Engineer intern, I have seen some queries similar to your query statement that did not have any subquery.

Subqueries allow for compartmentalized data analysis or data refinement.

If you would like to know more about subqueries, go to this link which is a post on StackOverFlow about the understanding of subqueries. Use SQL W3schools link and read about the IN, LIKE, HAVING, ANY, and ALL SQL commands to start a subquery.

To use subqueries, or not to use subqueries, that is up to you.

stevenwbroussard
  • 26
  • 2