Table Join using unique values or where Null = all values

Question

Post reply

Table Join using unique values or where Null = all values

DerbyNeal

Hall of Fame

Points: 3854
More actions
March 20, 2013 at 7:35 am

#271889

Hi folks,
I have a complex query that involves many tables but I am struggling with one particular join (TableA and TableB). There is only one field in each table where a join can be made. TableA.Area contains values that can be matched with TableB. However TableA may also contain a Null value in the joining column. If there is a Null value, then TableA.Area needs to join with all values in TableB.ColId.
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableA](
[Id] [tinyint] IDENTITY(1,1) NOT NULL,
[Area] [nvarchar](10) NULL,
[Item] [nvarchar](10) NOT NULL,
[Description] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
;
INSERT INTO dbo.TableA
(
Area, Item, [Description]
)
VALUES('A1', 'B1232', 'Thingymebobs')
;
INSERT INTO dbo.TableA
(
Area, Item, [Description]
)
VALUES('A4', 'B4352', 'Whatits')
;
INSERT INTO dbo.TableA
(
Area, Item, [Description]
)
VALUES(Null, 'B8769', 'Dunno')
;
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableB](
[ColId] [nvarchar](10) NOT NULL,
[DeliveryName] [nvarchar](60) NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[ColId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
;
INSERT INTO dbo.TableB
(
ColId, DeliveryName
)
VALUES('A1', 'Acme1')
;
INSERT INTO dbo.TableB
(
ColId, DeliveryName
)
VALUES('A4', 'Acme4')
;
INSERT INTO dbo.TableB
(
ColId, DeliveryName
)
VALUES('A8', 'Acme8')
;
INSERT INTO dbo.TableB
(
ColId, DeliveryName
)
VALUES('A9', 'Acme9')
;
The columns to be joined are TableA.Area AND TableB.ColId
SELECT b.DeliveryName, a.Item, a.[Description]
FROM
TableA a JOIN Tableb b ON --????
;
Any ideas please?
Thanks in advance,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Horatiu SSChasing Mays Points: 652 More actions · Answer 1

Something like this ?

SELECT b.DeliveryName, a.Item, a.[Description]

FROM

TableA a JOIN Tableb b ON (a.Area = b.ColId) or (a.Area is null)

There is always something new to learn.
My personal SQL Blog[/url]

raym85 SSC Eights! Points: 938 More actions · Answer 2

I think maybe you're looking for something like this?

SELECT DISTINCT *

FROM(

SELECT * FROM #TableA

where Area IS NULL )LL

FULL OUTER JOIN #TableB CB ON LL.Area = cb.ColId

LEFT OUTER JOIN (SELECT *

FROM #TableA a

JOIN #TableB b ON a.Area = b.ColId)PP

ON PP.Area = CB.ColId

0409SQL SSChasing Mays Points: 650 More actions · Answer 3

Hi There

I think it would be better if you could provide us the format of output required.

Anyway try this:

SELECT A.Area, B.DeliveryName, A.Item, A.Description

FROM

TableA A inner join TableB B ON (A.Area = B.ColId) or (A.Area is NULL)

order by A.Area desc

Output:

---------

AreaDeliveryNameItemDescription

A4Acme4B4352Whatits

A1Acme1B1232Thingymebobs

NULLAcme1B8769Dunno

NULLAcme4B8769Dunno

NULLAcme8B8769Dunno

NULLAcme9B8769Dunno

Not sure if this is what you need ! 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

The spec isn't entirely clear; does this generate the results you're expecting to see?

SELECT b.*, a.*, x.*

FROM TableB b

LEFT JOIN TableA a ON b.ColId = a.Area

LEFT JOIN TableA x ON x.Area IS NULL AND a.Area IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden