InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:54:48 GMT20http://www.sqlservercentral.com/Forums/Topic1433202-392-1.aspxThe spec isn't entirely clear; does this generate the results you're expecting to see?[code="sql"]SELECT b.*, a.*, x.* FROM TableB bLEFT JOIN TableA a ON b.ColId = a.AreaLEFT JOIN TableA x ON x.Area IS NULL AND a.Area IS NULL[/code]Wed, 20 Mar 2013 10:31:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1433202-392-1.aspxHi ThereI think it would be better if you could provide us the format of output required. Anyway try this:[i]SELECT A.Area, B.DeliveryName, A.Item, A.DescriptionFROM TableA A inner join TableB B ON (A.Area = B.ColId) or (A.Area is NULL)order by A.Area desc[/i]Output:---------Area DeliveryName Item DescriptionA4 Acme4 B4352 WhatitsA1 Acme1 B1232 ThingymebobsNULL Acme1 B8769 DunnoNULL Acme4 B8769 DunnoNULL Acme8 B8769 DunnoNULL Acme9 B8769 DunnoNot sure if this is what you need ! :-)Wed, 20 Mar 2013 09:32:44 GMTLove0409SQLhttp://www.sqlservercentral.com/Forums/Topic1433202-392-1.aspxI think maybe you're looking for something like this?SELECT DISTINCT * FROM( SELECT * FROM #TableAwhere Area IS NULL )LLFULL OUTER JOIN #TableB CB ON LL.Area = cb.ColId LEFT OUTER JOIN (SELECT * FROM #TableA aJOIN #TableB b ON a.Area = b.ColId)PPON PP.Area = CB.ColIdWed, 20 Mar 2013 08:39:51 GMTraym85http://www.sqlservercentral.com/Forums/Topic1433202-392-1.aspxSomething like this ?[code="sql"]SELECT b.DeliveryName, a.Item, a.[Description]FROM TableA a JOIN Tableb b ON (a.Area = b.ColId) or (a.Area is null)[/code]Wed, 20 Mar 2013 07:47:42 GMTHoratiuhttp://www.sqlservercentral.com/Forums/Topic1433202-392-1.aspxHi 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 [b]all[/b] values in TableB.ColId. [code="sql"]USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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');[/code]The columns to be joined are TableA.Area AND TableB.ColId[code="sql"]SELECT b.DeliveryName, a.Item, a.[Description]FROM TableA a JOIN Tableb b ON --????;[/code]Any ideas please?Thanks in advance,Wed, 20 Mar 2013 07:35:22 GMTDerbyNeal