Select row where value exists in another row - usually the next row

Transact-SQL
1

Hello,
I've come to the end of minimal tsql knowledge and are hoping that someone may be able to help?

I have a table which contain relationship data, where each row holds data such as ID, name, relationship and reciprocal relationship type, start and finish date etc.

So for each relationship there are two rows for each side of the relationship, and each row also holds the recipricol ID (see example below).

What I am trying to do is display one of the paired rows .i.e. those rows in the example where the ID is 1234 or 1236.

In most cases the ID's for the paired rows will be sequential as per the example but that isn't always the case.

I hope all of that makes sense?
Paul

+------+-------+------------+------------+----------+------------+---------+
| ID | Name | Start | Finish | Type | Recip Type | RecipID |
+------+-------+------------+------------+----------+------------+---------+
| 1234 | Joe | 01/05/2018 | | Father | Daughter | 1235 |
+------+-------+------------+------------+----------+------------+---------+
| 1235 | Emily | 01/05/2018 | | Daughter | Father | 1234 |
+------+-------+------------+------------+----------+------------+---------+
| 1236 | Susan | 01/09/2017 | 01/05/2018 | Visitor | Patient | 1237 |
+------+-------+------------+------------+----------+------------+---------+
| 1237 | Harry | 01/09/2017 | 01/05/2018 | Patient | Visitor | 1236 |
+------+-------+------------+------------+----------+------------+---------+

2

Would you provide usable data -- CREATE TABLE and INSERT statements -- rather than just a "picture" of data?

3

Hi Scott,

Having never had the pleasure of inserting code before I hope I have done this correctly.

Thank you
Paul

/****** Object: Table [dbo].[RELTEST] Script Date: 05/10/2018 10:08:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[RELTEST]( [ID] [int] NOT NULL, [RECIPROCAL_ID] [int] NULL, [Relationship] [varchar](100) NOT NULL, [RecipRelationship] [varchar](100) NOT NULL, [PrimaryImport_ID] [varchar](20) NULL, [DATE_ADDED] [datetime] NOT NULL, [DATE_FROM] [varchar](8) NULL, [DATE_TO] [varchar](8) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707016, 707017, N'Employee', N'Employer', N'00001-518-0000707016', CAST(0x0000A84A00EDFF5E AS DateTime), N'20171215', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707017, 707016, N'Employer', N'Employee', N'00001-518-0000707017', CAST(0x0000A84A00EDFF75 AS DateTime), N'20171215', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707047, 707046, N'Employer', N'Employee', N'00001-518-0000707047', CAST(0x0000A84A0108669D AS DateTime), NULL, NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707340, 707341, N'Navigator', N'PWM', N'00001-518-0000707340', CAST(0x0000A84E00C8809E AS DateTime), N'20171219', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707341, 707340, N'PWM', N'Navigator', N'00001-518-0000707341', CAST(0x0000A84E00C880AE AS DateTime), N'20171219', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707382, 707383, N'FS Service Provider', N'PWM', N'00001-518-0000707382', CAST(0x0000A84E00E501BB AS DateTime), N'20171219', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707388, 707389, N'PWM', N'Visitor', N'00001-518-0000707388', CAST(0x0000A84E00EB2E97 AS DateTime), N'20171219', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707389, 707388, N'Visitor', N'PWM', N'00001-518-0000707389', CAST(0x0000A84E00EB2EA4 AS DateTime), N'20171219', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707479, 0, N'Organisation', N'PWM', N'00001-518-0000707479', CAST(0x0000A84E011F531F AS DateTime), NULL, NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707550, 707551, N'Visitor', N'PWM', N'00001-518-0000707550', CAST(0x0000A84F00CC3720 AS DateTime), N'20171209', NULL) INSERT [dbo].[RELTEST] ([ID], [RECIPROCAL_ID], [Relationship], [RecipRelationship], [PrimaryImport_ID], [DATE_ADDED], [DATE_FROM], [DATE_TO]) VALUES (707551, 707550, N'PWM', N'Visitor', N'00001-518-0000707551', CAST(0x0000A84F00CC372B AS DateTime), N'20171209', NULL)

4

another cleaner and easier for the eye way is as follows

CREATE TABLE #RELTEST( [ID] [int] NOT NULL, 
                       [RECIPROCAL_ID] [int] NULL, 
					   [Relationship] [varchar](100) NOT NULL, 
					   [RecipRelationship] [varchar](100) NOT NULL, 
					   [PrimaryImport_ID] [varchar](20) NULL, 
					   [DATE_ADDED] [datetime] NOT NULL, 
					   [DATE_FROM] [varchar](8) NULL, 
					   [DATE_TO] [varchar](8) NULL ) 
					   ON [PRIMARY] 
GO 
insert into #RELTEST
select 707016, 707017, N'Employee', N'Employer', N'00001-518-0000707016', 
CAST(0x0000A84A00EDFF5E AS DateTime), N'20171215', NULL 
union all
select 707017,  707016, N'Employer', N'Employee', N'00001-518-0000707017', 
CAST(0x0000A84A00EDFF75 AS DateTime), N'20171215', NULL 
union all		 
select 707047, 707046, N'Employer', N'Employee', N'00001-518-0000707047', 
CAST(0x0000A84A0108669D AS DateTime), NULL, NULL
union all	 
select 707340, 707341, N'Navigator', N'PWM', N'00001-518-0000707340', 
CAST(0x0000A84E00C8809E AS DateTime), N'20171219', NULL
union all	
select 707341, 707340, N'PWM', N'Navigator', N'00001-518-0000707341', 
CAST(0x0000A84E00C880AE AS DateTime), N'20171219', NULL
union all
select 707382, 707383, N'FS Service Provider', N'PWM', N'00001-518-0000707382', 
CAST(0x0000A84E00E501BB AS DateTime), N'20171219', NULL
union all 
select 707388, 707389, N'PWM', N'Visitor', N'00001-518-0000707388', 
CAST(0x0000A84E00EB2E97 AS DateTime), N'20171219', NULL 
union all 
select 707389, 707388, N'Visitor', N'PWM', N'00001-518-0000707389', 
CAST(0x0000A84E00EB2EA4 AS DateTime), N'20171219', NULL 
union all
select 707479, 0, N'Organisation', N'PWM', N'00001-518-0000707479', 
CAST(0x0000A84E011F531F AS DateTime), NULL, NULL
union all 
select 707550, 707551, N'Visitor', N'PWM', N'00001-518-0000707550', 
CAST(0x0000A84F00CC3720 AS DateTime), N'20171209', NULL
union all 
select 707551, 707550, N'PWM', N'Visitor', N'00001-518-0000707551', 
CAST(0x0000A84F00CC372B AS DateTime), N'20171209', NULL

drop table #RELTEST
2 Likes
5

If you want only one of the paired rows -- that is, don't list unpaired rows at all -- then this:

SELECT pair1.* 
FROM dbo.RELTEST pair1
WHERE ID < RECIPROCAL_ID AND EXISTS (
    SELECT 1
    FROM dbo.RELTEST pair2
    WHERE pair2.ID = pair1.RECIPROCAL_ID AND
        pair2.RECIPROCAL_ID = pair1.ID
    )
1 Like
6

Hi Yosiaz,

Thank you for the advice I'll take that on board for future reference.

Paul

7

Hi Scott,

Apologies for the delay in getting back to you.

That worked a treat, thank you very much for your help and advice.

Paul