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