match records if exists

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

I have two tables:

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.