table subtraction challenge
- by Valentin
I have a challenge that I haven’t overcome in the last two days using Stored Procedures and SQL 2008.
I took several approaches but must fell short.
One appraoch very interesting was using a table substraction.
It’s really all about table subtraction.
I was wondering if you could help me crack this one.
Here is the challenge:
Two tables 1Testdb y 2Testdb.
My first step was to select ID relationships ([2Testdb].Acc_id) on table 2Testdb for one given individual ([2Testdb].Bus_id). Then query table 1Testdb for records not mathcing my original selection from 2Testdb.
But other approaches are welcome.
Data and Structures:
USE [Challengedb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[1Testdb](
[Acc_id] [uniqueidentifier] NULL
[Name] [Varchar(10)] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[2Testdb](
[Acc_id] [uniqueidentifier] NULL,
[Bus_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
Records on 1Testdb:
34455F60-9474-4521-804E-66DB39A579F3, John
C23523F6-2309-4F58-BB3F-EF7486C7AF8B, Pete
DC711615-3BE4-4B31-9EF2-B1314185CA62, Dave
E3AAB073-2398-476D-828B-92829F686A4C, Adam
Records on 2Testdb: (Relationship table, ex. Friend relationships)
Record #1: DC711615-3BE4-4B31-9EF2-B1314185CA62, 34455F60-9474-4521-804E-66DB39A579F3
Record #2: E3AAB073-2398-476D-828B-92829F686A4C, 34455F60-9474-4521-804E-66DB39A579F3
Record # 3: DC711615-3BE4-4B31-9EF2-B1314185CA62, E3AAB073-2398-476D-828B-92829F686A4C
Record # 4: E3AAB073-2398-476D-828B-92829F686A4C, DC711615-3BE4-4B31-9EF2-B1314185CA62
Challenge: Select from table 1Testdb only those records distinct that may not have a relationship with John [34455F60-9474-4521-804E-66DB39A579F3] on table 2Testdb.
Expected result should be (Who does John doesn’t have relationship with?):
C23523F6-2309-4F58-BB3F-EF7486C7AF8B, Pete
Thank you,
Valentin
