CODEWITHSUNDEEP
sqlsql-server
Oleg Sh
Oleg Sh

Reputation: 9013

SQL Server: select records, not linked to another table

I have a table:

CREATE TABLE [dbo].[CollectionSite]
(
    [SiteCode] [nvarchar](32) NOT NULL,
    [AddressId] [int] NOT NULL,
    [RemittanceId] [int] NULL,
    // additional columns
)

and a linked table:

CREATE TABLE [dbo].[CollectionSiteAddress]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NULL,
    [Address1] [nvarchar](255) NULL,
    [Address2] [nvarchar](255) NULL,
    [City] [nvarchar](128) NULL,
    [State] [nvarchar](64) NULL,
    [Zip] [nvarchar](32) NULL,
)

Relationship between these 2 tables:

ALTER TABLE [dbo].[CollectionSite] WITH CHECK 
    ADD CONSTRAINT [FK_CollectionSite_CollectionSiteAddress_AddressId] 
        FOREIGN KEY([AddressId]) REFERENCES [dbo].[CollectionSiteAddress] ([Id])
GO

ALTER TABLE [dbo].[CollectionSite]  WITH CHECK 
    ADD CONSTRAINT [FK_CollectionSite_CollectionSiteAddress_RemittanceId]  
        FOREIGN KEY([RemittanceId]) REFERENCES [dbo].[CollectionSiteAddress] ([Id])
GO

I want to select all records from CollectionSiteAddress, which are not linked to CollectionSite (neither AddressId nor RemittanceId). Which request should I use?

I tried:

SELECT *
FROM CollectionSiteAddress 
LEFT JOIN CollectionSite ON CollectionSiteAddress.Id = CollectionSite.AddressId 
                         OR CollectionSiteAddress.Id = CollectionSite.RemittanceId

but it selects all records from CollectionSiteAddress

Upvotes: 0

Views: 322

Answers (1)

forpas
forpas

Reputation: 164099

You are missing this WHERE clause:

WHERE CollectionSite.[SiteCode] IS NULL

because you want all the unmatched rows of CollectionSiteAddress.
I used the column [SiteCode] to check if it is NULL because it is not nullable in the definition of the table.
So you can write your query like this (shortened with aliases):

SELECT csa.*
FROM CollectionSiteAddress csa LEFT JOIN CollectionSite cs
ON csa.Id = cs.AddressId OR csa.Id = cs.RemittanceId
WHERE cs.[SiteCode] IS NULL

Or use NOT EXISTS:

SELECT csa.*
FROM CollectionSiteAddress csa 
WHERE NOT EXISTS (
  SELECT 1 
  FROM CollectionSite cs
  WHERE csa.Id = cs.AddressId OR csa.Id = cs.RemittanceId
)

Upvotes: 1

Related Questions