Cross apply query on the same table SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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)

Posted 8-Apr-20 0:47am

CPuser2020

Updated 9-Apr-20 17:49pm

Add a Solution

Comments

Richard MacCutchan 8-Apr-20 7:26am

What errors?

CPuser2020 8-Apr-20 7:30am

Hi Richard, I've sorted it by splitting the the where:
select distinct * from authorise where UID IN (
select top 1 UID from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID) AND AccountNumber IN(
select top 1 ACCOUNTNUMBER from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID)

Maciej Los 8-Apr-20 8:27am

If you resolved your issue, please delete this question or provide an answer.

#realJSOP 8-Apr-20 8:55am

Provide the answer is the desired path because it will help someone else that has a similar issue.

CPuser2020 8-Apr-20 10:37am

Hi the above takes a few seconds to run in the sql server manager but takes ages when running from app calling the stored procedure using sqladapter. I've read that this be because of the sub queires. Just wondering is there a better solutiton?

2 solutions

Solution 2

SELECT M.* FROM (
SELECT AutheriseID, UID, Name, AccountNumber, AccountName, Match, Frequency
FROM authorise
WHERE Frequency=1
) M INNER JOIN authorise A ON M.UID=A.UID AND M.NAME=A.NAME
AND A.AutheriseID<>M.AutheriseID AND A.UID IN (SELECT TOP 1 UID FROM authorise ORDER BY UID )

Posted 9-Apr-20 17:50pm

NEGIN_K

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPuser2020 · Accepted Answer · 2020-04-08T03:47:00

select * from authorise where UID IN (
select top 1 UID from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID) AND AccountNumber IN(
select top 1 ACCOUNTNUMBER from authorise where NOT EXISTS (SELECT *
FROM trained
WHERE authorise.name = trained.name AND
authorise.accountnumber = trained.accountnumber) and frequency = 1 order by UID)