title: SQL Recursive query as subquery
body: <p>I'm working with a SQL database here that contains a table called <code>CONTACT</code> where I store data related to a contact as well as it's source so I can track where it came from. One particular field in the <code>CONTACT</code> table is called <code>CONTACT_SOURCE</code> while another field <code>CONTACT_SOURCE_CONTACT_ID</code> is a self-join to this same table on <code>CONTACT_ID</code>. What I'm attempting to do is display <code>CONTACT</code> data including with where it came from when I join on other tables in my database such as <code>QUOTE</code>, <code>ORDER</code>, etc.</p>
  
  <p>However, rather than a standard <code>SELF-JOIN</code>, I'm trying to deal with the recursive scenario here. For example, my field <code>CONTACT_SOURCE_CONTACT_ID</code> is where I store the <code>CONTACT_ID</code> of another contact in this same <code>CONTACT</code> table that verbally referred this contact to my business. What I'm attempting to do here is recursively trace a contact using this <code>CONTACT_SOURCE_CONTACT_ID</code> field to display as their source the source of this contact that referred them to me. For example, if John found me on Social Media and told Anne who then told Bob who ended up submitting a quote, I want to associate Bob with John's source (via Anne) and therefore display Social Media as his source, and therefore the source of the quote.</p>
  
  <pre><code>WITH CTE
  AS (
  /*Anchor query that returns CONTACT records without a corresponding CONTACT_SOURCE_CONTACT_ID record*/
  SELECT C1.CONTACT_ID, C1.CONTACT_SOURCE
  FROM CONTACT AS C1
  WHERE CONTACT_SOURCE_CONTACT_ID IS NULL
  
  UNION ALL
  
  /*Recursive query that returns CONTACT records with a corresponding CONTACT_SOURCE_CONTACT_ID record*/
  SELECT C2.CONTACT_ID, CTE.CONTACT_SOURCE
  FROM CONTACT AS C2
  INNER JOIN CTE ON CTE.CONTACT_ID = C2.CONTACT_SOURCE_CONTACT_ID
  WHERE C2.CONTACT_SOURCE_CONTACT_ID IS NOT NULL)
  
  SELECT QUOTE.QUOTE_ID, CONTACT.CONTACT_ID, CONTACT.CONTACT_NAME, CTE.CONTACT_SOURCE
  FROM CTE
  INNER JOIN CONTACT ON CTE.CONTACT_ID = CONTACT.CONTACT_ID
  INNER JOIN QUOTE ON QUOTE.QUOTE_CONTACT_ID = CONTACT.CONTACT_ID
  ORDER BY CTE.CONTACT_ID;
  </code></pre>
  
  <p>I've built a <a href="http://sqlfiddle.com/#!3/6bd35/5/0" rel="nofollow" title="SQL Fiddle">SQL Fiddle example</a> that demonstrates this using test data as well as the recursive SQL query I built to accomplish this and it works great!</p>
  
  <p>This all makes sense, but I'm just curious if I'm doing this correctly and if this is efficient when I use this recursive query as a subquery. Shouldn't I have a reference to the <code>QUOTE_CONTACT_ID</code> field within the recursive query? I feel like I'm checking all recursive relationships in my entire <code>CONTACT</code> table instead of just the <code>CONTACT</code> records returned as a result of <code>SELECT * FROM dbo_QUOTE INNER JOIN dbo_CONTACT ON CONTACT_ID = QUOTE_CONTACT_ID</code>. Let's say I have 2,000 <code>QUOTE</code> records but 12,000 <code>CONTACT</code> records. When I just want to see <code>QUOTE</code> records I only want the recursive query to run for the <code>CONTACT</code> records that have a corresponding record in the <code>QUOTE</code> table.</p>
  
  <p>Does this make sense? Thanks in advance for any advice or tips!</p>