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!

To much words. Tired to read this all. What is the concrete problem? — Giorgi Nakeuri, Oct 30 '15 at 15:06
@GiorgiNakeuri Sorry. Trying to give it enough context. I have a recursive query here that works great, but I'm wondering how to efficiently integrate a recursive query as a subquery when joining on other tables. I don't want to recursively check all records in my `CONTACT` table and then join it with other tables, I want to only recursively check `CONTACT` records that satisfy the `JOIN` when joining on other tables. When I integrate this recursive query as a subquery it slows my query down noticeably. — mntyguy, Oct 30 '15 at 15:10
you can certainly get the contact information you need in the cte also instead of joining to contact again — JamieD77, Oct 30 '15 at 16:05

score 1 · Answer 1 · answered Oct 30 '15 at 16:29

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

answered Oct 30 '15 at 16:29

Hogan

69,564
10
76
117

Have you worked with hierarchy data types a lot with Entity Framework? – JamieD77 Oct 30 '15 at 16:31
1

@JamieD77 - No, when I see Entity Framework I run screaming. – Hogan Oct 30 '15 at 16:35
Lol.. You're probably glad wcf data services are going away too then. – JamieD77 Oct 30 '15 at 16:39
@JamieD77 - an open standard API? It can't go away, it is open. They are totally different things. The problem with Entity Framework is that it is "easy". This means is management believes entry level can create complex systems with it. As always this rarely works out well, then they hire me to fix it. – Hogan Oct 30 '15 at 16:44
expecting entry level people to create complex systems is the issue, not entity framework.. – JamieD77 Oct 30 '15 at 17:59
Entity framework does not work well for anything that is not simple-- but for some people think it will :) – Hogan Oct 30 '15 at 18:12

JamieD77 · Answer 2 · 2015-10-30T16:29:14.673

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;

SQL Recursive query as subquery

2 Answers2

Linked