CODEWITHSUNDEEP
sqlsql-server
JumpIntoTheWater
JumpIntoTheWater

Reputation: 1336

Get one row of grouped objects

I have a table that contains a Husband to Wife realtion. The table contains two rows for each realtion(BTW,gender has no meaning.it could be Husband-Husband and Wife-Wife. just saying). meaning, the table might show result of two rows for a "connection": Wife--Husband and\or Husband--Wife The table looks like this:

 Id1    | Id2  | ConnectiondID  |  RelatedConnectionId
 -----------------------------------------------------
 123    | 333  |    FF45        |        F421
 333    | 123  |    F421        |        FF45
 456    | 987  |    F333        |        F321
 987    | 456  |    F321        |        F333

My expected result is to have only one relation per group:

Id1  | Id2
----------
123  | 333
456  | 987

Upvotes: 2

Views: 61

Answers (3)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

For example

DECLARE @T TABLE (id1 int, id2 int,ConnectiondID varchar(5),RelatedConnectionId varchar(5) )
 INSERT INTO @T (Id1,Id2,ConnectiondID,RelatedConnectionId)
 VALUES
 (123    , 333 ,'FF45','F421'),
 (333    , 123 , 'F421','FF45'),
 (2123    , 2333 ,'2FF45','2F421'),
 (2333    , 2123 , '2F421','2FF45'),
 (3    , 2 , 'AAAA','BBB'),
 (2    , 3 , 'BBB','AAAA')

SELECT  
    a.*
FROM 
    @t a
WHERE 
    CASE 
    WHEN ConnectiondID > RelatedConnectionId
    THEN RelatedConnectionId
    ELSE NULL 
    END IS NULL

Upvotes: 0

iamdave
iamdave

Reputation: 12243

This is actually very simple assuming you only want couples and your ID values are all unique and numeric, and does not require any self joins, functions or grouping:

declare @t table(Id1 int,Id2 int,ConnectiondID nvarchar(5),RelatedConnectionId nvarchar(5));
insert into @t values(123,333,'FF45','F421'),(333,123,'F421','FF45'),(456,444,'FF46','F422'),(444,456,'F422','FF46'),(789,555,'FF47','F423'),(555,789,'F423','FF47');

select *
from @t
where Id1 < Id2
order by Id1

Output:

+-----+-----+---------------+---------------------+
| Id1 | Id2 | ConnectiondID | RelatedConnectionId |
+-----+-----+---------------+---------------------+
| 123 | 333 | FF45          | F421                |
| 444 | 456 | F422          | FF46                |
| 555 | 789 | F423          | FF47                |
+-----+-----+---------------+---------------------+

Upvotes: 1

SQLBadPanda
SQLBadPanda

Reputation: 635

If I am understanding your question correctly, you need to perform a self-join on the table e.g. ON t1.id1 = t2.id2 or ON t1.ConnectionId = t2.RelatedConnectionID and obviously this is joining both ways.

To limit this to just one way add a condition on the join predicate such that one of the values is less than or greater than the other; e.g.

DECLARE @tbl table( Id1 smallint PRIMARY KEY, Id2 smallint,ConnectiondID char(5),RelatedConnectionId char(5));
INSERT @tbl(Id1,Id2,ConnectiondID,RelatedConnectionId)
 VALUES(123,333,'FF45','F421'),
       (333,123,'F421','FF45'),
       (456,222,'FF45','F421'),
       (222,456,'F421','FF45'),
       (789,111,'FF45','F421'),
       (111,789,'F421','FF45');

SELECT * 
FROM @tbl t1
JOIN @tbl t2 ON t2.Id1 = t1.Id2 AND t2.Id1 > t1.Id1;

Upvotes: 0

Related Questions