SQL Recursive query as subquery

Question

I'm working with a SQL database here that contains a table called CONTACT 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 CONTACT table is called CONTACT_SOURCE while another field CONTACT_SOURCE_CONTACT_ID is a self-join to this same table on CONTACT_ID. What I'm attempting to do is display CONTACT data including with where it came from when I join on other tables in my database such as QUOTE, ORDER, etc.

However, rather than a standard SELF-JOIN, I'm trying to deal with the recursive scenario here. For example, my field CONTACT_SOURCE_CONTACT_ID is where I store the CONTACT_ID of another contact in this same CONTACT table that verbally referred this contact to my business. What I'm attempting to do here is recursively trace a contact using this CONTACT_SOURCE_CONTACT_ID 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.

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;

I've built a SQL Fiddle example that demonstrates this using test data as well as the recursive SQL query I built to accomplish this and it works great!

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 QUOTE_CONTACT_ID field within the recursive query? I feel like I'm checking all recursive relationships in my entire CONTACT table instead of just the CONTACT records returned as a result of SELECT * FROM dbo_QUOTE INNER JOIN dbo_CONTACT ON CONTACT_ID = QUOTE_CONTACT_ID. Let's say I have 2,000 QUOTE records but 12,000 CONTACT records. When I just want to see QUOTE records I only want the recursive query to run for the CONTACT records that have a corresponding record in the QUOTE table.

Does this make sense? Thanks in advance for any advice or tips!

Answer 1

You can work with hierarchical data using recursive CTEs (what you have here is hierarchical data) but that does not mean you should.

Microsoft specifically provides functionality to work with hierarchical data -- it has been designed and optimized specifically for these scenarios. As such it will work best for these kind of problems.

More information: https://msdn.microsoft.com/en-us/library/bb677173.aspx

Answer 2

This might help some.. You can get all of the information you need in your CTE for your query..

WITH CTE AS (
    SELECT
        Q.QUOTE_ID,
        C.CONTACT_ID,
        C.CONTACT_NAME,
        C.CONTACT_SOURCE
    FROM
        QUOTE AS Q
        INNER JOIN CONTACT AS C ON Q.QUOTE_CONTACT_ID = C.CONTACT_ID
    WHERE
        CONTACT_SOURCE_CONTACT_ID IS NULL
    UNION ALL
    SELECT
        Q.QUOTE_ID,
        C.CONTACT_ID,
        C.CONTACT_NAME,
        CTE.CONTACT_SOURCE
    FROM
        QUOTE AS Q
        INNER JOIN CONTACT AS C ON Q.QUOTE_CONTACT_ID = C.CONTACT_ID
        INNER JOIN CTE ON CTE.CONTACT_ID = C.CONTACT_SOURCE_CONTACT_ID
    WHERE
        C.CONTACT_SOURCE_CONTACT_ID IS NOT NULL
)
SELECT
    *
FROM
    CTE
ORDER BY
    CTE.CONTACT_ID;

honestly after looking at the query plans i'd stick with what you have.. and just add contact_name to your cte query so you dont have to join to Contact again ..

WITH CTE AS (
    SELECT
        C1.CONTACT_ID,
        C1.CONTACT_SOURCE,
        C1.CONTACT_NAME
    FROM
        CONTACT AS C1
    WHERE
        CONTACT_SOURCE_CONTACT_ID IS NULL
    UNION ALL
    SELECT
        C2.CONTACT_ID,
        CTE.CONTACT_SOURCE,
        C2.CONTACT_NAME
    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,
    CTE.CONTACT_ID,
    CTE.CONTACT_NAME,
    CTE.CONTACT_SOURCE
FROM
    CTE
    INNER JOIN QUOTE ON QUOTE.QUOTE_CONTACT_ID = CTE.CONTACT_ID
ORDER BY
    CTE.CONTACT_ID;