TSQL - Return rows based on the value in another row
-
Hello,
I hope someone may be able to help?I have table which holds relationship data where most of the relationships are reciprocated, but not all.
Where the relationship is reciprocated there will be a pair of rows holding the reciprocated data and in these instances the PK for each row will be stored in the ID and RECIPROCAL_ID columns.
In a query I would like to return all non reciprocated rows along with one of the paired rows (it doesn't matter which). So in essence it is matching the ID column with the RECIPROCAL_ID column on another row.
I have tried numerous ways to get this to work using self joins, in, exists statements etc but haven't found anything which will provide the results I'm looking for.
I have included a Create and Insert statement to provide some test data, but being new to support forums I hope I have done this correctly.
Thank you in advance for any help.
Paul
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)
- pe2618 - Thursday, May 10, 2018 4:33 AM
Surely if you want rows whose ID match the value of RECIPROCAL_ID in another row, that's the reciprocated rows, not the non reciprocated rows, isn't it? Please will you show us what results you expect from your sample data (which was spot-on, by the way)?
John
-
Hi John,
Thank you for the quick response.In the sample data there are 4 reciprocated relationship records with ID's - 70716-70717, 707340-707341, 707388-707389 and 707550-707551.
With that in mind I would like one row of each of reciprocated relationship. i.e. 70716, 707340, 707388 and 707550 plus all of the non reciprocated rows.
I have attached a screenshot of the test data and highlighted the rows I would like returning in the query.
I hope this makes some sense now.
Thank you
Paul
-
Sample data in the form of an image isn't very helpful, most of the time. Other people can't interact with it; you can't copy the text and create a table with it. Try to provide your data by form of a DDL statement, and INSERT scripts. There's a link in my signature that should help you on provide your sample information in a consumable format.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, May 10, 2018 7:18 AM
Hi Thom,
Are you not seeing the Create and Insert code in my original post which creates the sample data shown in the image?
I'm newish to support forums so I don't get some of the ettiquette yet so that image should have been a direct reply back to answer Johns question.
Sorry for any confusion.
Paul - pe2618 - Thursday, May 10, 2018 7:32 AM
My bad, sorry! Yoiu are indeed right. 🙂
What, however, would your output look like then? Rather than an image showing us the relevant rows, what do you want the final dataset to look like?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, May 10, 2018 7:40 AM
You don't need to apologise to me! 🙂
-
Paul
This will get you somewhere near where you want to be. It involves three table scans, so there may be a better alternative from a performance point of view.SELECT
ID
, RECIPROCAL_ID
, Relationship
, RecipRelationship
, PrimaryImport_ID
, DATE_ADDED
, DATE_FROM
, DATE_TO
FROM RELTEST
EXCEPT
SELECT
r1.ID
, r1.RECIPROCAL_ID
, r1.Relationship
, r1.RecipRelationship
, r1.PrimaryImport_ID
, r1.DATE_ADDED
, r1.DATE_FROM
, r1.DATE_TO
FROM RELTEST r1
JOIN RELTEST r2
ON r1.ID = r2.RECIPROCAL_ID
AND r1.RECIPROCAL_ID = r2.ID
WHERE r1.ID > r1.RECIPROCAL_IDJohn
- John Mitchell-245523 - Thursday, May 10, 2018 7:44 AM
John, would that not be more easily acheived as?
SELECT R1.*
FROM RELTEST R1
LEFT JOIN RELTEST r2 ON r1.ID = r2.RECIPROCAL_ID AND r1.RECIPROCAL_ID = r2.ID AND r1.ID > r1.RECIPROCAL_ID
WHERE R2.ID IS NULL;
It cuts down on 1 of the table scans at least.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, May 10, 2018 7:51 AM
Absolutely - I'd just worked that out for myself and was about to post!
John
- John Mitchell-245523 - Thursday, May 10, 2018 7:52 AM
Gents,
I'm about to leave for the day, so I will test it on the live data tomorrow and get back to you.
Thank you
Paul - John Mitchell-245523 - Thursday, May 10, 2018 7:52 AM
Hi Gents,
Apologies for not getting back to you sooner, but just to let you know that worked a treat thank you.Is there a rating system on this forum?
If so how does it work and can I share the points?
Once again thank you very much for your help and advice.
Paul - pe2618 - Friday, May 11, 2018 8:09 AM
SSC works quite differently to SO, if you're thinking of points. You can, however, mark a post as the solution by flicking the little tick near the bottom left of the reply, or give them a thumbs up by clicking the thumbs up button. both award "points". Generally a simple thank you is very well received here though. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Just another go at this, which does 4 scans, but should produce accurate results:
SET NOCOUNT ON;CREATE TABLE #RELTEST (
ID int NOT NULL PRIMARY KEY CLUSTERED,
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
);
GOSET ANSI_PADDING OFF;
GOINSERT #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),
(707017, 707016, N'Employer', N'Employee', N'00001-518-0000707017', CAST(0x0000A84A00EDFF75 AS DateTime), N'20171215', NULL),
(707047, 707046, N'Employer', N'Employee', N'00001-518-0000707047', CAST(0x0000A84A0108669D AS DateTime), NULL, NULL),
(707340, 707341, N'Navigator', N'PWM', N'00001-518-0000707340', CAST(0x0000A84E00C8809E AS DateTime), N'20171219', NULL),
(707341, 707340, N'PWM', N'Navigator', N'00001-518-0000707341', CAST(0x0000A84E00C880AE AS DateTime), N'20171219', NULL),
(707382, 707383, N'FS Service Provider', N'PWM', N'00001-518-0000707382', CAST(0x0000A84E00E501BB AS DateTime), N'20171219', NULL),
(707388, 707389, N'PWM', N'Visitor', N'00001-518-0000707388', CAST(0x0000A84E00EB2E97 AS DateTime), N'20171219', NULL),
(707389, 707388, N'Visitor', N'PWM', N'00001-518-0000707389', CAST(0x0000A84E00EB2EA4 AS DateTime), N'20171219', NULL),
(707479, 0, N'Organisation', N'PWM', N'00001-518-0000707479', CAST(0x0000A84E011F531F AS DateTime), NULL, NULL),
(707550, 707551, N'Visitor', N'PWM', N'00001-518-0000707550', CAST(0x0000A84F00CC3720 AS DateTime), N'20171209', NULL),
(707551, 707550, N'PWM', N'Visitor', N'00001-518-0000707551', CAST(0x0000A84F00CC372B AS DateTime), N'20171209', NULL);SET STATISTICS IO ON;
SET STATISTICS TIME ON;WITH RECIPROCALS AS (
SELECT R.*,
CASE
WHEN R.ID < R.RECIPROCAL_ID THEN 1
ELSE 2
END AS Grp
FROM #RELTEST AS R
WHERE EXISTS (
SELECT 1
FROM #RELTEST AS R2
WHERE R2.RECIPROCAL_ID = R.ID
)
),
NON_RECIPROCALS AS (SELECT R.*,
CONVERT(int, NULL) AS Grp
FROM #RELTEST AS R
WHERE NOT EXISTS (
SELECT 1
FROM #RELTEST AS R2
WHERE R2.RECIPROCAL_ID = R.ID
)
)
SELECT 'RECIPROCALS' AS Indicator, R1.ID, R1.RECIPROCAL_ID, R1.Relationship, R1.RecipRelationship,
R1.PrimaryImport_ID, R1.DATE_ADDED, R1.DATE_FROM, R1.DATE_TO
FROM RECIPROCALS AS R1
WHERE R1.Grp = 1
UNION ALL
SELECT 'NON-RECIPROCALS' AS Indicator, R2.ID, R2.RECIPROCAL_ID, R2.Relationship, R2.RecipRelationship,
R2.PrimaryImport_ID, R2.DATE_ADDED, R2.DATE_FROM, R2.DATE_TO
FROM NON_RECIPROCALS AS R2
ORDER BY ID;SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;DROP TABLE #RELTEST;
Here's the STATISTICS:
SQL Server parse and compile time:
CPU time = 11 ms, elapsed time = 11 ms.
Table '#RELTEST____________________________________________________________________________________________________________000000000064'. Scan count 4, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply