Query that returns a unique ID for each different clause matched?

All we need is an easy explanation of the problem, so here it is.

I’m in a situation where I’m being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information. Actual user data from matched users, along with the unique ID for that match, needs to be returned from my SQL query. So, let’s say I were given 2 entities to try and match users against, both of which were trying to match phone numbers against a user’s phone number; I could match any users associated with the entities submitted along with the "match entity"’s unique ID by using a union like this (client_handle is the submitted unique ID):

SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

The trouble with this method is that it could potentially result in a very large number of UNIONs if large numbers of match entities are submitted to me. 1000 submitted match entities would result in 999 UNIONs. Is this actually an issue performance-wise, and is there a better way to achieve the result I want? Alternatively I could just loop through each submitted match entity and run a query to match each, but then I’d have 1000 separate queries if 1000 match entities are submitted, which seems even worse.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Schematically:

CREATE TABLE tmp ([parameters group] INT, [client_handle] CHAR(11), [mobile_no] CHAR(15));

INSERT INTO tmp VALUES
(1,'axtwe-wasst','+44 7747 122123'),
(1,'axtwe-wasst','+44 7904 223323'),
(2,'zjfft-albwq','+44 7758 444111'),
(2,'zjfft-albwq','+44 7758 444222'),
(2,'zjfft-albwq','+44 7758 444333');

SELECT DISTINCT
    [client_handle] = tmp.[parameters group],
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive] t
JOIN 
    tmp ON t.[mobile_no] = tmp.[mobile_no];

PS. [parameters group] is not used – on shown source data it is excess, but may be useful in real task (if so then GROUP BY instead of DISTINCT must be used).

Method 2

What I ended up doing in the end was passing in JSON representing the different set of queries, and breaking them into a table using OPENJSON. This actually makes the query simple enough that it doesn’t even need to be in a stored procedure or create a temp. table, as OPENJSON can be used directly in a JOIN query, being a table-valued function:

SELECT
    *,
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[vAPP_UsersActive] usr
    INNER JOIN (
        SELECT * FROM OPENJSON(@jsonQuery) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255))
    ) qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])

… where the @jsonQuery variable is passed in with a value like:

[{"UniqueId":"123aaa","MobileNo":null,"Email":"[email protected]"},{"UniqueId":"123aaa","MobileNo":null,"Email":"[email protected]"},{"UniqueId":"234bbb","MobileNo":"+44 7121 234588","Email":null},{"UniqueId":"234bbb","MobileNo":"+44 7121 234599","Email":null},{"UniqueId":"234bbb","MobileNo":"+44 7759 112233","Email":null}]

The calling code first has to flatten out the queries with the same UniqueId into multiple "query rows" as seen in the JSON above, and then unflattens the matches returned with a dictionary whose key is the UniqueId.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply