SQL queries with foreign keys or database is hierarchical

In this article I intend to discuss a way to query database(relational : unique data is stored in a row of table) using SQL when
1) Hierarchical Data is stored in database.
2) There are 2 or more tables and 1 table contains a column which holds the reference to 1st table.

Lets take case 1 :
For hierarchical database say your schema for storing geographical information inside a "RegionTable" is :
ColumnName       Type        //   Purpose
ParentRowID          int          //  ID of parent row in this table, and it is 0 for top-most-element in hierarchy. (used to mantain hierarchy)
RegionName         String    //  Name of region, could be countryName, StateName, CityName, BlockName etc.
Population              long       // Population of the region

 1 0 India
 2 1 NCR 6
 3 1 Bangalore 2
 4 2 Noida 1

Now suppose your aim is to fetch all immediate regions lying inside India from table above. In that case there are 2 ways to do it :
First Method -  (requires 2 queries)
      SELECT  rowid   FROM RegionTable WHERE RegionName =  "India"  ;
      // stores the returned rowid into a variable say "RowNumber". or note it down and pass it in next query.
     SELECT  RegionName FROM RegionTable WHERE ParentRowID = RowNumber  ;

Second Method - (requires 1 query only)
      SELECT r2.RegionName FROM RegionTable as r1, RegionTable as r2 WHERE r1.RegionName = "India"  and  r2.ParentRowID =  r1.rowid   ;

Now second method, as you can see is more elegant and requires only single query.   Little explaination of this, I think is needed here.
What I have done is aliased table "RegionTable"  by two different identifiers i.e. "r1"  and "r2".  So this creates 2 virtual views of same table.

After it, I have decided that I would use "r1" view for looking up "India" inside the table.
Hence   condition on r1 becomes  :-     r1.RegionName = "India"

And would use "r2" view for looking up children of   records obtained from "r1" view.
Hence condition on r2 becomes  :-      r2.ParentRowID   =   r1.rowid

Combine these 2 conditions using "and" operator and you are almost done.

Now part left is "r2.RegionName"  immediately after SELECT.  Reason for this is that, you need to clarify from which view you finally want information. 

Note: - "rowid" is internally created column in every table.

I would finish Case 2 in a short while. Its a just another example of  usage we seen in second-method above.

comments powered by Disqus