Geek asks and answers

Extract data from two tables with an indirect relation (from another table)

I have 3 tables

Division 

id    name  
1       A 
2       B


Region

Id       name     Divsion_id
01      Rim          A


Territory

Id       name       region_id

001      a               01
002      b               01
003      c               01

Now I want to write a query so that the user selects a section and its corresponding territories should be displayed.

How can I write this request since

no direct connection between Divsion and Region?

+3
source share
2 answers
select t.* from territory t
inner join region r on r.id = t.region_id
inner join devision d on d.name = r.division_id
where d.name = 'A'
+4
source

Another approach would be to use an operator INwith a subquery. Internal query projects in all regions, specifying a region identifier and an external query, search for territories with selected regions. Although the connections should perform better than the subquery.

  select * from territory where region_id   in  (select region_id from region where devision_id=<division id>)
0
source

More articles:


All Articles