CODEWITHSUNDEEP
t-sqlsql-server-2014
Anon Dev
Anon Dev

Reputation: 1411

Find the first record that match any element in another table

I'm looking for an efficient way of accomplishing this in T-SQL (SQL Server 2014)

I have a static base elements table variable like this:

╔════╦═════════════╗
║ Id ║   Hashtag   ║
╠════╬═════════════╣
║  1 ║ Thailand    ║
║  2 ║ Philippines ║
║  3 ║ Indonesia   ║
║  4 ║ Brazil      ║
║  5 ║ Mexico      ║
║  6 ║ Nicaragua   ║
║  7 ║ Colombia    ║
║  8 ║ Malaysia    ║
╚════╩═════════════╝

And I also have another table variable (dynamically populated with a query) like this:

╔════╦══════════╗
║ Id ║ Hashtag  ║
╠════╬══════════╣
║  1 ║ Live     ║
║  2 ║ Brazil   ║
║  3 ║ Like     ║
║  4 ║ Thailand ║
║  5 ║ Malaysia ║
║  6 ║ Love     ║
╚════╩══════════╝

What I need to do is iterate over the second table and for each record look if it exists in the base table and when it finds the first match stop the iteration and return the found value, in my example the query should return "Brazil" because is the first record in descending order that is also contained in the base table.

I'm currently doing this using a Fast Forward Cursor with the second table elements and querying the first table to look for the match, it works but I'm not an expert in T-SQL so I ask for your help to know if there is a more efficient way.

Upvotes: 0

Views: 61

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521379

As an alternative to Shree's answer, we can use row number here to identify the first matching record as ordered by the Id:

WITH cte AS(
    SELECT t2.Hashtag,
           ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) rn
    FROM table2 t2
    INNER JOIN table1 t1
        ON t2.Hashtag = t1.Hashtag
)
SELECT t.Hashtag
FROM cte
WHERE t.rn = 1

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

Assuming you want to find a single record which is the first match, you can do something like this:

select hashtag 
from dynamictable
where id = (
            select min(d.id) 
            from dynamictable d 
            inner join referencetable r on r.hashtag = v.hashtag
           )

The inner join only returns records where hashtag exists in both tables. Then we simply select the minimum ID from your dynamic table and get the corresponding hashtag as output.

In fact, this can be further simplified to use a single join as follows:

select top 1 d.hashtag
from dynamictable d 
inner join referencetable r on r.hashtag = d.hashtag
order by v.id

Upvotes: 2

Related Questions