Table Join using unique values or where Null = all values

  • 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,

  • 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]

  • 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

  • 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 ! ๐Ÿ™‚

  • 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

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

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