Sub Query Returns All Results

user_2521288 0
Viewed 46

The query below gives me a nested json object of users and draws assigned to them but the sub-query is returning all draws for all users and not for the user in the row. I think I'm missing a WHERE clause in the sub query but not quite sure what to use.

    SELECT (
        SELECT 
            d.user_id,
            u.name as name, 
         (SELECT 
                d.user_id, 
                s.status_name, 
                d.last_name, 
                d.loan_number, 
                d.request_date, 
                d.date_funded 
                FROM Draws d
                Left Join Users u
                on d.user_id = u.id 
                Left Join DrawStatus s
                on s.id = d.status_id
                WHERE u.name = name
                GROUP BY u.name, d.assignee_id, s.status_name, d.last_name, d.loan_number, d.request_date, d.date_funded
                For JSON PATH) Draws
            FROM Draws d
            Left Join Users u
            on d.assignee_id = u.id
            GROUP BY u.name, d.user_id
            FOR JSON PATH) Processor
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

The draw count is correct for each processor but each Draw array is pulling in all of the draws instead of just the ones for this row.

{
   "Processor":[
      {
         "assignee_id":26,
         "name":"Processor One",
         "draw_count":7,
         "Draws":[
            {
               "assignee_id":26,
               "status_name":"Change Orders Signed",
               "last_name":"Smith",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Elevation Certificate",
               "last_name":"Troy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Inspection",
               "last_name":"Rameson",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Lien Releases",
               "last_name":"Hartley",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"DaSilva",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Survey",
               "last_name":"Gasser",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Title Update",
               "last_name":"Sherman",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Inspection",
               "last_name":"Valcruse",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"Tommy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Title Update",
               "last_name":"Ronnie",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Elevation Certificate",
               "last_name":"Franklin",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Lien Releases",
               "last_name":"Bamwell",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Survey",
               "last_name":"Roscoe",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            }
         ]
      },
      {
         "assignee_id":28,
         "name":"Processor 2",
         "draw_count":3,
         "Draws":[
            {
               "assignee_id":26,
               "status_name":"Change Orders Signed",
               "last_name":"Smith",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Elevation Certificate",
               "last_name":"Troy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Inspection",
               "last_name":"Rameson",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Lien Releases",
               "last_name":"Hartley",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"DaSilva",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Survey",
               "last_name":"Gasser",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Title Update",
               "last_name":"Sherman",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Inspection",
               "last_name":"Valcruse",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"Tommy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Title Update",
               "last_name":"Ronnie",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Elevation Certificate",
               "last_name":"Franklin",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Lien Releases",
               "last_name":"Bamwell",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Survey",
               "last_name":"Roscoe",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            }
         ]
      },
      {
         "assignee_id":30,
         "name":"Processor 3",
         "draw_count":3,
         "Draws":[
            {
               "assignee_id":26,
               "status_name":"Change Orders Signed",
               "last_name":"Smith",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Elevation Certificate",
               "last_name":"Troy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Inspection",
               "last_name":"Rameson",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Lien Releases",
               "last_name":"Hartley",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"DaSilva",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Survey",
               "last_name":"Gasser",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":26,
               "status_name":"Title Update",
               "last_name":"Sherman",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Inspection",
               "last_name":"Valcruse",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Payment Affidavit\/Notarized AIA",
               "last_name":"Tommy",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":28,
               "status_name":"Title Update",
               "last_name":"Ronnie",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Elevation Certificate",
               "last_name":"Franklin",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Lien Releases",
               "last_name":"Bamwell",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            },
            {
               "assignee_id":30,
               "status_name":"Survey",
               "last_name":"Roscoe",
               "loan_number":"123456",
               "request_date":"2020-03-11T00:00:00",
               "date_funded":"2020-03-11T00:00:00"
            }
         ]
      }
   ]
}
sql-server sql
1 Answers

Since you're using the same table aliases in both the inner and outer queries, it's hard to tell for sure but I don't think you have a correlation from inner to outer set up. Unless it's the unqualified name column in the u.name = name clause. But I think there might be an issue with that because the alias wouldn't be recognized yet during the evaluation of the inner query, so it may just be reading that as an equality of u.name = u.name and not filtering at all (which is what you're seeing, I guess.)

In any case, it's the lack of (perhaps meaningful) correlation that's tripping you up. This might do it? But you'll have to play around with it a little, depending on your data and which joins and correlations are truly meaningful in your data.

SELECT (
        SELECT 
             d2.user_id
            ,u2.name AS name
            ,(
                SELECT d.user_id
                    ,s.status_name
                    ,d.last_name
                    ,d.loan_number
                    ,d.request_date
                    ,d.date_funded
                FROM Draws d
                LEFT JOIN Users u
                    ON d.user_id = u.id
                LEFT JOIN DrawStatus s
                    ON s.id = d.status_id
                WHERE u.name = name
                  AND d.user_id = d2.user_id  --< Added. Plus added 2s to the outer aliases.
                GROUP BY u.name
                    ,d.assignee_id
                    ,s.status_name
                    ,d.last_name
                    ,d.loan_number
                    ,d.request_date
                    ,d.date_funded
                FOR JSON PATH
                ) Draws
        FROM Draws d2
        LEFT JOIN Users u2
            ON d2.assignee_id = u2.id
        GROUP BY u2.name
            ,d2.user_id
        FOR JSON PATH
        ) Processor
FOR JSON PATH
    ,WITHOUT_ARRAY_WRAPPER
user_57905840
Related