volvo
March 3. 2021 0 Comments
- Posted in:
- test car
<article>
<p>By: <a href="/sqlserverauthor/296/jayendra-viswanathan/" rel="author" title="author profile for Jayendra Viswanathan">Jayendra Viswanathan</a> | Updated: 2018-06-04 | <a href="#comments" title="read and post comments">Comments (3)</a> | Related: <a href="/sql-server-dba-resources/">More</a> > <a href="/sql-server-tip-category/228/sql-server-2017/">SQL Server 2017</a></p>
<br>
<h5>Problem</h5>
<p>I came across Graph processing in SQL Server which to me is an advanced version
of using <a href="/sql-server-tip-category/26/scripts/">Common Table Expressions</a> (CTE). SQL Server offers graph database capabilities
to model many-to-many relationships. The graph relationships are integrated into
T-SQL and receive the benefits of using SQL Server as the foundations database management
system. In simple words, a graph database is the combination of NODES and EDGES.
NODES represent an entity, EDGES represent a relationship between two nodes. Both
nodes and edges may have properties associated with them. In this tip we are
going to see a hierarchical structure of a family tree. The family tree will be
based on the chart below. We will use SQL Server graph functionality to store and
retrieve the data.</p>
<h5>Solution</h5>
<p>Here is a family tree that we will use for this tip.</p>
<div class="imageborder">
<img alt="William family chart - Description: William family chart" src="/tipimages2/5429_graph-processing-sql-server-2017.001.png">
</div>
<p>William was born in 1850 and is the father of the family. The above chart is self-explanatory
with names of William's sons and the year of birth. Let’s process the
above chart with Graph processing with SQL Server.</p>
<p>First, we are going to create the base data for William's family. Then create
a NODE table which will create nodes for each record. Create an EDGE table which
will be used to match the NODEs of William's family. The EDGE table will contain
the actual output. We will use MATCH SQL command to virtually create the descendent
tables as NODES and EDGES to fetch records per our needs.</p>
<p>We can use the below SQL commands to create the base data for William's family.
I created a fresh database too.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">DROP</span><span class="pln"> </span><span class="kwd">DATABASE</span><span class="pln"> </span><span class="kwd">IF</span><span class="pln"> </span><span class="kwd3">EXISTS</span><span class="pln"> FamilyDB</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">GO</span><span class="pln">
</span><span class="kwd">CREATE</span><span class="pln"> </span><span class="kwd">DATABASE</span><span class="pln"> FamilyDB</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">GO</span><span class="pln">
</span><span class="kwd">USE</span><span class="pln"> FamilyDB</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">DROP</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> </span><span class="kwd">IF</span><span class="pln"> </span><span class="kwd3">EXISTS</span><span class="pln"> MyFamily</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">CREATE</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> MyFamily </span><span class="pun">(</span><span class="pln">
FmlyNum </span><span class="typ">NUMERIC</span><span class="pun">(</span><span class="lit">8</span><span class="pun">)</span><span class="pln"> </span><span class="kwd3">not</span><span class="pln"> </span><span class="kwd3">null</span><span class="pun">,</span><span class="pln">
Name </span><span class="typ">VARCHAR</span><span class="pun">(</span><span class="lit">40</span><span class="pun">)</span><span class="pln"> </span><span class="kwd3">NOT</span><span class="pln"> </span><span class="kwd3">NULL</span><span class="pun">,</span><span class="pln">
FmlyLink </span><span class="typ">NUMERIC</span><span class="pun">(</span><span class="lit">8</span><span class="pun">),</span><span class="pln">
YOB </span><span class="typ">INT</span><span class="pun">,</span><span class="pln">
INUM </span><span class="typ">INT</span><span class="pun">)</span><span class="pln"></span></pre>
</div>
<p>The above statements will create the base table “MyFamily”. </p>
<p>Let's insert the records. The top most 1st level is William who was born in
1850 and hence William is kept in the top most node and the FmlyLink column is kept
as NULL. Other records have a FmlyLink code for their father. Each record is linked
between the FmlyNum and FmlyLink column. </p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFamily </span><span class="kwd">values</span><span class="pln">
</span><span class="pun">(</span><span class="lit">10000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'William'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd3">NULL</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1850</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">140000</span><span class="pun">,</span><span class="str">'ALLEN'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">10000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1877</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">60000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ROBINSON'</span><span class="pun">,</span><span class="lit">140000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1902</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">70000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'DAVIS'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">140000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1903</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">80000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADAM'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">140000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1904</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">90000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'SCOTT'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">140000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1905</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">100000</span><span class="pun">,</span><span class="str">'NELSON'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">140000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1906</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">20000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'GONZALEZ'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1876</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">30000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'LEWIS'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">20000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1901</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">31000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'GRANT'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">30000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1926</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">40000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'WALKER'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">10000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1875</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">120000</span><span class="pun">,</span><span class="str">'YOUNG'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">40000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1900</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">50000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'HARRIS'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">120000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1925</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">130000</span><span class="pun">,</span><span class="str">'MITCHELL'</span><span class="pun">,</span><span class="lit">120000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1926</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">110000</span><span class="pun">,</span><span class="str">'CAMPBELL'</span><span class="pun">,</span><span class="lit">130000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1951</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">150000</span><span class="pun">,</span><span class="str">'BLACK'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">130000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1952</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">160000</span><span class="pun">,</span><span class="str">'WHITE'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">150000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1977</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">170000</span><span class="pun">,</span><span class="str">'JAMES'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">160000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2002</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">);</span><span class="pln">
</span></pre>
</div>
<p>By running the above INSERT statements, the base data of William's family is
ready.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="auto-style1"><span class="kwd">SELECT</span></span><span style="background-color: #ffffff"><span class="pln"> * </span><span class="kwd">FROM</span><span class="pln"> MyFamily</span></span><span class="pln"></span></pre>
</div>
<div class="imageborder">
<img alt="graph database sample data" src="/tipimages2/5429_graph-processing-sql-server-2017.002.png">
</div>
<h2>Node Table</h2>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">DROP</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> </span><span class="kwd">IF</span><span class="pln"> </span><span class="kwd3">EXISTS</span><span class="pln"> MyFmlyNode</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">CREATE</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> MyFmlyNode</span><span class="pun">(</span><span class="pln">
FNO </span><span class="typ">Int</span><span class="pln"> </span><span class="kwd">Identity</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">),</span><span class="pln">
FmlyNum </span><span class="typ">NUMERIC</span><span class="pun">(</span><span class="lit">8</span><span class="pun">)</span><span class="pln"> </span><span class="kwd3">NOT</span><span class="pln"> </span><span class="kwd3">NULL</span><span class="pun">,</span><span class="pln">
Name </span><span class="typ">VARCHAR</span><span class="pun">(</span><span class="lit">40</span><span class="pun">),</span><span class="pln">
FmlyLink </span><span class="typ">NUMERIC</span><span class="pun">(</span><span class="lit">8</span><span class="pun">),</span><span class="pln">
INUM </span><span class="typ">INT</span><span class="pln">
</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> NODE</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFmlyNode</span><span class="pun">(</span><span class="pln">FmlyNum</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> FmlyLink</span><span class="pun">,</span><span class="pln"> INUM</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> FmlyNum</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> FmlyLink</span><span class="pun">,</span><span class="pln"> INUM
</span><span class="kwd">FROM</span><span class="pln"> MyFamily
</span><span class="kwd">SELECT</span><span class="pln"> * </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode</span></pre>
</div>
<div class="imageborder">
<img alt="node table output" src="/tipimages2/5429_graph-processing-sql-server-2017.003.png">
</div>
<p>We have created a NODE table and inserted the records with $NODE_ID as the key
column. </p>
<p>Let's see how {"type":"node","schema":"dbo","table":"MyFmlyNode","id":0}
can be explained: </p>
<ul>
<li>The first column is the $node_id, this column is automatically created with
the user defined columns that is created with the “AS NODE” keyword
in the CREATE statement. In our case we have FNO, FmlyNum, Name and FmlyLink
are user created columns. The NODE table is an entity in a graph schema. The
values in the $node_id column are automatically generated and are a combination
of object_id of that node table and an internally generated bigint value. We
can also create a unique constraint or index on the $node_id column.</li>
</ul>
<h2>Edge Table</h2>
<p>An EDGE table maintains the relationship between two NODES. In the below statement
we will extract the matching $node_id’s from the NODE table and insert the
value in the EDGE table, so that unique graph ids are created to maintain a relationship.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">DROP</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> </span><span class="kwd">IF</span><span class="pln"> </span><span class="kwd3">EXISTS</span><span class="pln"> MyFmlyEdge</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">CREATE</span><span class="pln"> </span><span class="kwd">TABLE</span><span class="pln"> MyFmlyEdge</span><span class="pun">(</span><span class="pln">
FmlyNum </span><span class="typ">numeric</span><span class="pun">(</span><span class="lit">8</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> EDGE
</span><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFmlyEdge
</span><span class="kwd">SELECT</span><span class="pln"> e</span><span class="pun">.$</span><span class="pln">node_id</span><span class="pun">,</span><span class="pln"> m</span><span class="pun">.$</span><span class="pln">node_id </span><span class="pun">,</span><span class="pln">e</span><span class="pun">.</span><span class="pln">fno
</span><span class="kwd">FROM</span><span class="pln"> dbo</span><span class="pun">.</span><span class="pln">MyFmlyNode e
</span><span class="kwd3">INNER</span><span class="pln"> </span><span class="kwd3">JOIN</span><span class="pln"> dbo</span><span class="pun">.</span><span class="pln">MyFmlyNode m </span><span class="kwd">ON</span><span class="pln"> e</span><span class="pun">.</span><span class="pln">FmlyNum </span><span class="pun">=</span><span class="pln"> m</span><span class="pun">.</span><span class="pln">FmlyLink</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> * </span><span class="kwd">FROM</span><span class="pln"> MyFmlyEdge</span></pre>
</div>
<p>Let’s discuss the SELECT query part of the above INSERT statement. If you
notice the INSERT statement into the Edge table, the two NODEs from the MyFmlyNode
table are extracted based on the key column e.FmlyNum = m.FmlyLink. Now the query
returns the matching $node_id data from Node tables. The extracted $node_id’s
are inserted into the EDGE table. By INSERTING the matching NODE’s into the
EDGE table, the link/connection is established between the Node and Edge tables.
</p>
<p>Below is the output of the SELECT statement for entire William's family. </p>
<div class="imageborder">
<img alt="edge table output" src="/tipimages2/5429_graph-processing-sql-server-2017.004.png">
</div>
<p>I would like to also touch base on that the above join query can also be performed
by using INSERT statements into the EDGE table by using a WHERE clause from the
NODE table. Here are a few sample INSERT statements for understanding how this can
be done.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFmlyEdge
</span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">((</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="lit">111</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFmlyEdge
</span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">((</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln">
</span><span class="lit">111</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">INSERT</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> MyFmlyEdge
</span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">((</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="kwd">SELECT</span><span class="pln"> </span><span class="pun">$</span><span class="pln">node_id </span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode </span><span class="kwd">WHERE</span><span class="pln"> FNO </span><span class="pun">=</span><span class="pln"> </span><span class="lit">4</span><span class="pun">),</span><span class="pln">
</span><span class="lit">111</span><span class="pun">);</span><span class="pln"></span></pre>
</div>
<p>After running the above INSERT statements, the below query will show us the output
from the EDGE table. </p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">SELECT</span><span class="pln"> * </span><span class="kwd">FROM</span><span class="pln"> MyFmlyEdge</span></pre>
</div>
<div class="imageborder">
<img alt="egde table output" src="/tipimages2/5429_graph-processing-sql-server-2017.005.png">
</div>
<p>Now the EDGE table MyFmlyEdge is loaded with matched records for William's
Family.</p>
<p>I am going to cleanup these records we just inserted, so our results are correct
for the family tree that we started with.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">DELETE</span><span class="pln"> </span><span class="kwd">FROM</span><span class="pln"> MyFmlyEdge
</span><span class="kwd">WHERE</span><span class="pln"> FmlyNum </span><span class="pun">=</span><span class="pln"> </span><span class="lit">111</span><span class="pln"></span></pre>
</div>
<h2>Query data based on the chart</h2>
<p>Based on William’s family chart above, let's query the table and see
how we can get data.</p>
<h2>MATCH (SQL Graph)</h2>
<p>A match query can be used to query any SQL Graph, we can query the NODE and EDGE
using the MATCH statement. Nodes can be traversed an arbitrary number of times in
the same query. In our example, we will use the MATCH statement to traverse between
NODES and EDGES. MATCH can be used to traverse TOP DOWN or BOTTOM UP as per the
requirements.</p>
<h2>Second NODE query</h2>
<p>Return data for William and his sons.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">SELECT</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> MyFmlyNode2</span><span class="pun">.</span><span class="pln">name
</span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode MyFmlyNode1</span><span class="pun">,</span><span class="pln"> MyFmlyEdge</span><span class="pun">,</span><span class="pln"> MyFmlyNode MyFmlyNode2
</span><span class="kwd">WHERE</span><span class="pln"> MATCH</span><span class="pun">(</span><span class="pln">MyFmlyNode1-</span><span class="pun">(</span><span class="pln">MyFmlyEdge</span><span class="pun">)-></span><span class="pln">MyFmlyNode2</span><span class="pun">)</span><span class="pln">
</span><span class="kwd3">AND</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'William'</span><span class="pun">;</span><span class="pln"></span></pre>
</div>
<div class="imageborder">
<img alt="query results" src="/tipimages2/5429_graph-processing-sql-server-2017.006.png">
</div>
<p>If you notice William is the first node and the second nodes are ALLEN, GONZALEZ
and WALKER who are William's sons. MATCH is used to traverse the NODES from top
to bottom. To connect the two NODES we use this <strong>-></strong> that shows
we are going left to right.</p>
<p>Note that the relationship could go either way as shown below. </p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">SELECT</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> MyFmlyNode2</span><span class="pun">.</span><span class="pln">name
</span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode MyFmlyNode1</span><span class="pun">,</span><span class="pln"> MyFmlyEdge</span><span class="pun">,</span><span class="pln"> MyFmlyNode MyFmlyNode2
</span><span class="kwd">WHERE</span><span class="pln"> MATCH</span><span class="pun">(</span><span class="pln">MyFmlyNode1-</span><span class="pun">(</span><span class="pln">MyFmlyEdge</span><span class="pun">)-></span><span class="pln">MyFmlyNode2</span><span class="pun">)</span><span class="pln">
</span><span class="kwd3">AND</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'William'</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> MyFmlyNode2</span><span class="pun">.</span><span class="pln">name
</span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode MyFmlyNode1</span><span class="pun">,</span><span class="pln"> MyFmlyEdge</span><span class="pun">,</span><span class="pln"> MyFmlyNode MyFmlyNode2
</span><span class="kwd">WHERE</span><span class="pln"> MATCH</span><span class="pun">(</span><span class="pln">MyFmlyNode1</span><span class="pun"><-(</span><span class="pln">MyFmlyEdge</span><span class="pun">)-</span><span class="pln">MyFmlyNode2</span><span class="pun">)</span><span class="pln">
</span><span class="kwd3">AND</span><span class="pln"> MyFmlyNode2</span><span class="pun">.</span><span class="pln">name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'William'</span><span class="pun">;</span></pre>
</div>
<p>The first query shows William first and then his sons and the second query shows
the sons first and then William.</p>
<div class="imageborder">
<img alt="query results" src="/tipimages2/5429_graph-processing-sql-server-2017.009.png">
</div>
<h2>Third NODE Query</h2>
<p>This will return data for William, his sons and any son that has sons.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">SELECT</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln">MyFmlyNode2</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln">MyFmlyNode3</span><span class="pun">.</span><span class="pln">name
</span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode MyFmlyNode1</span><span class="pun">,</span><span class="pln"> MyFmlyEdge</span><span class="pun">,</span><span class="pln"> MyFmlyNode MyFmlyNode2</span><span class="pun">,</span><span class="pln"> MyFmlyEdge MyFmlyEdge2</span><span class="pun">,</span><span class="pln"> MyFmlyNode MyFmlyNode3
</span><span class="kwd">WHERE</span><span class="pln"> MATCH</span><span class="pun">(</span><span class="pln">MyFmlyNode1-</span><span class="pun">(</span><span class="pln">MyFmlyEdge</span><span class="pun">)-></span><span class="pln">MyFmlyNode2-</span><span class="pun">(</span><span class="pln">MyFmlyEdge2</span><span class="pun">)-></span><span class="pln">MyFmlyNode3</span><span class="pun">)</span><span class="pln">
</span><span class="kwd3">AND</span><span class="pln"> MyFmlyNode1</span><span class="pun">.</span><span class="pln">name </span><span class="pun">=</span><span class="pln"> </span><span class="str">'William'</span><span class="pun">;</span><span class="pln">
</span></pre>
</div>
<div class="imageborder">
<img alt="query results" src="/tipimages2/5429_graph-processing-sql-server-2017.007.png">
</div>
<ul>
<li>If you notice William is the first node </li>
<li>The second nodes are ALLEN, GONZALEZ and WALKER who are William's sons.
</li>
<li>The third nodes are
<ul>
<li>ROBINSON, DAVIS, ADAM, SCOTT, NELSON who are son's of ALLEN. </li>
<li>LEWIS is the son of GONZALEZ. </li>
<li>YOUNG is the son of WALKER</li>
</ul>
</li>
</ul>
<h2>Return Data for All Lineages</h2>
<p>To return data for all connections from the oldest to the most recent generation
we can use a query like below.</p>
<div class="codediv">
<pre class="prettyprint lang-sql prettyprinted" style=""><span class="kwd">with</span><span class="pln"> Fmly
</span><span class="kwd">AS</span><span class="pln">
</span><span class="pun">(</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> r1</span><span class="pun">.</span><span class="pln">NAME </span><span class="kwd">AS</span><span class="pln"> TopNode</span><span class="pun">,</span><span class="pln">r2</span><span class="pun">.</span><span class="pln">NAME </span><span class="kwd">AS</span><span class="pln"> ChildNode</span><span class="pun">,</span><span class="pln">CAST</span><span class="pun">(</span><span class="pln">CONCAT</span><span class="pun">(</span><span class="pln">r1</span><span class="pun">.</span><span class="pln">NAME</span><span class="pun">,</span><span class="str">'-<'</span><span class="pun">,</span><span class="pln">r2</span><span class="pun">.</span><span class="pln">NAME</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> </span><span class="typ">varchar</span><span class="pun">(</span><span class="lit">250</span><span class="pun">))</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> Output</span><span class="pun">,</span><span class="pln">r1</span><span class="pun">.$</span><span class="pln">node_id </span><span class="kwd">AS</span><span class="pln"> parentid</span><span class="pun">,</span><span class="pln"> r2</span><span class="pun">.$</span><span class="pln">node_id </span><span class="kwd">as</span><span class="pln"> bottomnode</span><span class="pun">,</span><span class="lit">1</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> Tree
</span><span class="kwd">FROM</span><span class="pln"> MyFmlyNode r1
</span><span class="kwd3">JOIN</span><span class="pln"> MyFmlyEdge e </span><span class="kwd">ON</span><span class="pln"> e</span><span class="pun">.$</span><span class="pln">from_id </span><span class="pun">=</span><span class="pln"> r1</span><span class="pun">.$</span><span class="pln">node_id
</span><span class="kwd3">JOIN</span><span class="pln"> MyFmlyNode r2 </span><span class="kwd">ON</span><span class="pln"> r2</span><span class="pun">.$</span><span class="pln">node_id </span><span class="pun">=</span><span class="pln"> e</span><span class="pun">.$</span><span class="pln">to_id </span><span class="kwd3">AND</span><span class="pln"> r1</span><span class="pun">.</span><span class="pln">NAME </span><span class="kwd3">IN</span><span class="pun">(</span><span class="pln"> </span><span class="str">'WILLIAM'</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">UNION</span><span class="pln"> </span><span class="kwd3">ALL</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> c</span><span class="pun">.</span><span class="pln">ChildNode</span><span class="pun">,</span><span class="pln">r</span><span class="pun">.</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">CAST</span><span class="pun">(</span><span class="pln">CONCAT</span><span class="pun">(</span><span class="pln">c</span><span class="pun">.</span><span class="pln">Output</span><span class="pun">,</span><span class="str">'-<'</span><span class="pun">,</span><span class="pln">r</span><span class="pun">.</span><span class="pln">NAME</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> </span><span class="typ">varchar</span><span class="pun">(</span><span class="lit">250</span><span class="pun">)),</span><span class="pln">c</span><span class="pun">.</span><span class="pln">bottomnode</span><span class="pun">,</span><span class="pln">r</span><span class="pun">.$</span><span class="pln">node_id</span><span class="pun">,</span><span class="pln">Tree </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
</span><span class="kwd">FROM</span><span class="pln"> Fmly c
</span><span class="kwd3">JOIN</span><span class="pln"> MyFmlyEdge e </span><span class="kwd">ON</span><span class="pln"> e</span><span class="pun">.$</span><span class="pln">from_id </span><span class="pun">=</span><span class="pln"> c</span><span class="pun">.</span><span class="pln">bottomnode
</span><span class="kwd3">JOIN</span><span class="pln"> MyFmlyNode r </span><span class="kwd">ON</span><span class="pln"> r</span><span class="pun">.$</span><span class="pln">node_id </span><span class="pun">=</span><span class="pln"> e</span><span class="pun">.$</span><span class="pln">to_id
</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">SELECT</span><span class="pln"> output </span><span class="kwd">FROM</span><span class="pln"> Fmly</span></pre>
</div>
<p>This shows all of the connections from left to right.</p>
<div class="imageborder">
<img alt="query results" src="/tipimages2/5429_graph-processing-sql-server-2017.008.png">
</div>
<h2>Conclusion</h2>
<p>We have seen how the MATCH clause can be used to fetch data from EDGE tables.
I hope the tip will be useful for the SQL Server developer community.</p>
<h5>Next Steps</h5>
<ul>
<li>Check out this tip on the
<a href="/sqlservertip/5373/sql-server-merge-statement-usage-and-examples/">
SQL Server MERGE command</a>
</li>
</ul>
<br><br>
Last Updated: 2018-06-04
<br><br><br>
<div class="row">
<div class="large-6 columns">
<div align="center" class="hide-for-print"><a href="/download-scripts/" onclick="_gaq.push(['_trackEvent', 'Scripts', 'Click', 'BotTip']);" target="_blank"><img border="0" src="/images/Free-Download_20190509.png" alt="get scripts" width="300" height="150"></a></div>
<br>
</div>
<div class="large-6 columns">
<div align="center" class="hide-for-print"><a href="/sql-server-tip-category/228/sql-server-2017/" onclick="_gaq.push(['_trackEvent', 'NextTip', 'Click', 'YellowArrow']);"><img border="0" src="/images/nav_nexttip_clickhere_rev1.png" alt="next tip button" width="300" height="150"></a></div>
<br>
</div>
</div>
<br><br>
<h5>About the author</h5>
<div class="row">
<div class="large-6 columns">
<a href="/sqlserverauthor/296/jayendra-viswanathan/"><img style="float: left; padding: 0px 3px 0px 0px;" alt="MSSQLTips author Jayendra Viswanathan" height="90" src="/images/Jayendra-Viswanathan.png" width="60"></a>
Jayendra is a Project Leader with many years of IT experience. He has strong knowledge in software development and project management.
<br><br><b><a href="/sqlserverauthor/296/jayendra-viswanathan/">View all my tips</a></b>
</div>
<div class="large-6 columns">
<b>Related Resources</b><br>
<ul>
<li><a href="/sql-server-dba-resources/">More SQL Server DBA Tips...</a></li>
</ul>
</div>
</div>
<br><br>
<a name="comments"></a>
</article>