table subtraction challenge

Question

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

Answer 1

Not sure exactly what you're asking; is it for all users in the first table that don't have a friendship with "John" based on the second table?

If so, use the "not exists" keyword to determine whether or not a record exists with the given query:

select a.*
from [1testdb] a
where not exists (
    select * from [2testdb] b where a.acc_id = b.acc_id and b.subid = '34455F60-9474-4521-804E-66DB39A579F3'
)
and a.acc_id <> '34455F60-9474-4521-804E-66DB39A579F3'

Answer 2

I'm not sure how your columns are set up...looks like GUIDs, but this is the SQL Server syntax for getting it to work. I included a case where John could be in either the Acc_id or Bus_id column, so that's why there are 2 joins instead of one.

Declare @id NVarchar(50)
Set @id = '34455F60-9474-4521-804E-66DB39A579F3'

Select *
From 1Testdb
Left Outer Join 2Testdb As ForwardRelationship On ForwardRelationship.Acc_id = @id And ForwardRelationship.Bus_id = 1Testdb.Acc_id
Left Outer Join 2Testdb As ReverseRelationship On ReverseRelationship.Bus_id = @id And ReverseRelationship.Acc_id = 1Testdb.Acc_id
Where
    ForwardRelationship.Acc_id Is Null And
    ForwardRelationship.Bus_id Is Null And
    ReverseRelationship.Acc_id Is Null And
    ReverseRelationship.Bus_id Is Null And
    1Testdb.Acc_id <> @id