match records if exists

2022-05-01T23:05:48.19+00:00

Hello, I need your help,

I have two tables and I need to check with specific conditions if a row from one table exists to another and return the id from the other.

For example, first I need to check if record exists with those conditions: date, vatnumber, series, invoicenumber
If nothing is found with those conditions I need to search with: date, vatnumber, invoicenumber
and e.t.c.

Which is the appropriate way to search? With a function to SQL, with LINQ c# ?
I want the most efficient way to do the search because is big tables.

thanks you very much

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,277 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,605 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2022-05-02T21:31:54.737+00:00

    For this type of question, it is always a good idea to post CREATE TABLE statements for your tables and INSERT statements with sample data, and the desired result given the sample. That makes it easy to copy and paste into a query window to develop a tested solution.

    To me it seems that the best would be:

    UPDATE table1
    SET   table2id = t2.id
    FROM   table1 t1
    JOIN   table2 t2 ON t1.date = t2.date
                     AND t2.series = t2.series
                     AND t2.invoicenr = t2.invoicenr
                     AND t2.vatnumber = t2.vatnumber
    WHERE  t1. table2id IS NULL
    
    UPDATE table1
    SET   table2id = t2.id
    FROM   table1 t1
    JOIN   table2 t2 ON t1.date = t2.date
                     AND t2.invoicenr = t2.invoicenr
                     AND t2.vatnumber = t2.vatnumber
    WHERE  t1. table2id IS NULL
    
    UPDATE table1
    SET   table2id = t2.id
    FROM   table1 t1
    JOIN   table2 t2 ON t1.date = t2.date
                     AND t2.invoicenr = t2.invoicenr
    WHERE  t1. table2id IS NULL
    
    1 person found this answer helpful.

  2. Naomi 7,366 Reputation points
    2022-05-02T01:54:19.36+00:00

    I would probably implement this as a stored procedure to keep the code inside the SQL Server and also to be able to optimize it. If you provide more details about your requirements, you may get better answer.

    0 comments No comments

  3. Bert Zhou-msft 3,421 Reputation points
    2022-05-02T02:15:54.797+00:00

    Hi,@ΜΑΡΙΑ ΝΙΚΟΛΟΠΟΥΛΟΥ

    Welcome to Microsoft T-SQL Q&A Forum!

    I will use exist or left join, Do you want to filter out records that meet the criteria of date-invoice and use them to match another table to find id?yes, stick to my opinion, left join is better .

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. 2022-05-02T15:46:08.973+00:00

    Hello, thank you all for your response.
    I will be more specific

    I have two tables:

    198313-image.png

    I want to return in column "Id of Table2" the Id of Table2 if the record exists.
    The first search I need to do is with date, series, invoicenr, vatnumber. If with those conditions I will find match I will return the Id. If not the second search I must do is with date, invoicenr, vatnumber and if i will find match i will return the Id.

    But sometimes I need to make more searches if the above search does not return anything or will return more than one record and of course, I need to inform the user of matchings and unmatching.
    I want to run searches in those order:
    Search 1: date, series, invoicenr, vatnumber = completely matching, if returns nothing then
    Search 2: date, invoicenr, vatnumber = completely matching, if returns nothing then
    Search 3: date, invoicenr

    In the end, if the match is found, I need to check many other columns net value, vat value, total value e.t.c. also is matching.

    I have done this as far:

        private void setLinkInvH(List<updLinkInvH> links)  
        {  
            if ((links != null) && (links.Count > 0))  
            {  
                List<gelRecs> GetGelRecs = new List<gelRecs>();  
                GetGelRecs = getGelRecs();  
                foreach (updLinkInvH r in links)  
                {  
                    int iRow = -1;  
                    //find with all mandatory conditions  
                    iRow = GetGelRecs.FindIndex(x => (  
                        x.Glh_Afm == r.sVatNumber && x.Glh_ArParastatikou == r.sAa  
                        && x.Glh_DateKin == r.sDate && x.Glh_Seira == r.sSeira  
                        ));  
                    //find with all conditions except "series"  
                    if (iRow < 0)  
                    {  
                        iRow = GetGelRecs.FindIndex(x => (  
                        x.Glh_Afm == r.sVatNumber && x.Glh_ArParastatikou == r.sAa  
                        && x.Glh_DateKin == r.sDate  
                        ));  
                    }  
                    if (iRow > -1)  
                    {  
                        r.lInvId = GetGelRecs[iRow].Glh_Id;  
                    }  
    
                }  
            }  
        }  
    

    But I need your opinion on the most efficient way to do this.

    0 comments No comments