SQL Server Re-querying the table with different values and adding the result

pbpqsu0x 于 4个月前发布在其他

关注(0)|答案(2)|浏览(49)

I have two tables. there is an input parameter. I need to perform a search on the first table, on the second table, and based on the search result on the second table, make a query to the first and output the combined result.

first step: 
select * from first table where id = 2
second step:
select * from second table where id = 2; result origid = 222
third step:
select * from first table where secondid = 222

first table:

id	text	secondid
1	text1	123
2	text2	321
3	text3	222
4	text4	101

second table:

id	text	origid
10	text1	111
2	text2	222
30	text3	333
44	text4	444

expected result:

id	text
2	text2
3	text3

sql-server

来源：https://stackoverflow.com/questions/77568594/re-querying-the-table-with-different-values-and-adding-the-result

2条答案

按热度按时间

zphenhs41#

You can use a query that combines both tables first_table and second_table using a subquery to filter results based on certain conditions.

DECLARE @Id int = 2;

SELECT id, text
FROM first_table t1
WHERE id = @Id OR secondid IN (
    SELECT origid
    FROM second_table t2
    WHERE t2.id = t1.id 
);

The above query retrieves rows from first_table where either the id matches the variable @Id or where the secondid matches an origid in the second_table based on the conditions specified.

赞(0）回复(0）举报 4个月前

ac1kyiln2#

You could use an OR query as in the other answer. But this sometimes has performance issues. If you do encounter issues (such as a big index scan), you can use a manual Index Union instead.

DECLARE @Id int = 2;

SELECT id, text
    FROM first_table t1
    WHERE id = @Id

UNION ALL

SELECT id, text
    FROM first_table
    WHERE secondid IN (
        SELECT origid
        FROM second_table t2
        WHERE t2.id = @Id
    );

If you could possibly get the same row twice then you need to change UNION ALL to UNION . Otherwise use UNION ALL as it is more efficient.

赞(0）回复(0）举报 4个月前

我来回答

SQL Server Re-querying the table with different values and adding the result

2条答案

相关问题

热门标签

最新问答