Jose Carlos Balantakbo Jose Carlos Balantakbo - 3 years ago 111
SQL Question

Select records that are in the intermediary table that matches a given value/values and records that are not in the intermediary table?

I have 2 tables

Id bigint primary key identity(1,1),

Table assignment serves as an intermediary table to table Floor

CREATE TABLE Assignment(
Id bigint primary key identity(1,1),
AccountId bigint,
FloorId bigint, --foreign key for Id column of floor table

How do I select:

  1. records from floor table given an XML which contains the accountId that matches accountId in the Assignment table.


DECLARE @accountIdsXML XML = '<AccountId>73</AccountId><AccountId>74</AccountId>'

  1. records that do not have matches in the Assignment table


Table values:

Floor Table
Id Name
1 1st Floor
2 2nd Floor
3 3rd Floor
4 4th Floor
5 5th Floor
6 6th Floor
7 7th Floor
8 8th Floor
9 9th Floor
10 10th Floor

Assignment Table
Id AccountId FloorId
1 73 1
1 73 2
1 76 3
1 79 4
1 74 5
1 74 6
1 79 7

Select Output:

Floor table
Id Name
1 1st Floor --matches accountid 73
2 2nd Floor --matches accountid 73
5 5th Floor --matches accountid 74
6 6th Floor --matches accountid 74
8 8th Floor --not in assignment table
9 9th Floor --not in assignment table
10 10th Floor --not in assignment table

I basically want to select floors that are assigned to the given accountid and unassigned floors.

What I have managed to do

DECLARE @accountIdsXML XML = '<AccountId>73</AccountId><AccountId>74</AccountId>'
DECLARE @accountIdsTable as TABLE (Id BIGINT) --table variable to store accountids to be used in the where in operator.

--insert accountids to the table variable
INSERT INTO @accountIdsTable
SELECT [aid].[Col].value('(.)[1]', 'BIGINT') FROM @accountIdsXML.nodes('/AccountId') as [aid]([Col])

SELECT F.* FROM [Assignment] A RIGHT JOIN [Floor] F ON A.FloorId = F.Id
WHERE (A.AccountId IN(SELECT Id from @accountIdsTable) OR F.Id NOT IN (SELECT FloorId FROM Assignment))

Answer Source

Don't select from Assignment and instead select only from Floor, then on the first WHERE clause you join both of them:

    SELECT 1 FROM [Assignment] A 
    INNER JOIN @accountIdsTable AI ON AI.Id = A.AccountId     
    WHERE A.FloorId = F.Id
OR F.Id NOT IN (SELECT FloorId FROM Assignment)

Here's a link the Fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download