Hi, I have a table that I need to self query on. Not too sure if it need to be done with a join or cross aply etc.
The table is :
Authorise(AutheriseID(PK), UID, Name, AccountNumber, AccountName, Match, Frequency)
I need to search the table for the top record with a frequency of 1 and also need to ma ke sure that the match isnt existing in another table already:
select top 1* from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID
This works fine. But then I also need to get the rest of the matches in the authorise table that match the select UID and accountnumber together. But have to make sure that is all that relates to the top 1 where frequency = 1.
So in my table if the following record was the result of:
<pre> select top 1* from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID
(10, 19, 'Test, 28, 'Test12', 'Test', 1)
And I wanted to get other matches that related to the same UID-19 and AccountNumber-28 then I would get more records that have matches and different frequencys. (The matches are substrings so i checks all the realted substrings to determine a good match on the full string) DSo if there was more matches to those UID and Accountnumber it might look like the following:
(10, 19, 'Test 12', 28, 'Test 12', 'Test', 1)
(134, 19, 'Test 12', 28, 'Test 12', '12', 4)
Meaning it has matched on two substrings of the original name Test and 12
What I have tried:
I have tried this but gives me errors:
select * from authorise where UID and AccountNumber esists(
select top 1 * from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.resellername = trained.resellername AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID)