I've got a SQL table like below where one value is linked to a second value and vice versa.

....

This might be some bad design but this is what I'm stuck with. I need to produce a SQL query in SQL Server to return only the following (doesn't matter which order):

....

OR

....

I've got a list of ID's (1, 2, 3, 4) which I used to query the table against ID1 field or ID2 field, but it always returns all the rows because those IDs exist in both columns.

I've tried looking at eliminating one row by looking if the one field it exists in the other column, but then I get no results. Obviously.

The one solution that could work is by looking at the rownum field and only get the even or odd rows. But this feels hacky. Also there might be other values in that list that is not part of my IN list, so that could possibly miss some rows?

Anything eloquent to consider from a TSQL perspective

CodePudding user response：

Here's one (quite cumbersome but pretty effective) way to do it.

First, Create and populate sample table (Please save us this step in future questions):

CREATE TABLE Table1 (
  [ROW] int, 
  [ID1] int, 
  [ID2] int
);
    
INSERT INTO Table1 ([ROW], [ID1], [ID2]) VALUES
(1, 1, 2),
(2, 2, 1),
(3, 3, 4),
(4, 4, 3),
(5, 1, 4); 

Note: The last raw is not a part of the sample data you've provided, but I assumed you would also like to include in the results records where only one row had the connection beteween Id1 and Id2.

Then, use a couple of common table expression to get the minimum row number of any pair of Id1 and Id2, regardless of the order of ids, and then query the original table joined to the second cte:

WITH CTE1 AS 
(
    SELECT Row, 
           IIF(Id1 < Id2, Id1, Id2) As Small,
           IIF(Id1 < Id2, Id2, Id1) As Big
    FROM Table1
), CTE2 AS 
(
    SELECT Min(Row) As MinRow
    FROM CTE1
    GROUP BY Small, Big
)

SELECT Row, Id1, Id2
FROM Table1
JOIN CTE2
    ON Row = MinRow;

Results:

Row Id1 Id2
1   1   2
3   3   4
5   1   4

You can see a live demo on DB<>Fiddle