How to find NULL values in joined table where no value in one

I think this is probably quite a simple problem, but maybe difficult to explain (hence the stupid title!) I have the following code in a procedure:


SELECT    *
FROM Comms LEFT OUTER JOIN
     LinkContact ON Comms.ID = LinkContacts.CommID LEFT OUTER JOIN
     Contacts ON LinkContact.ContactID = Contacts.ID
WHERE  LinkContact.ContactID = NULL

I have some rows in the Comms table which have no corresponding information in the LinkContact table, so returns NULL values for all the columns in the LinkContact and Contacts tables. However, when I try to do something like above in the WHERE I get no records returning! How do i fix this? Do i need to create a view first and then run my procedure against that?

I am using MSSQL 2005.

Cheers

monkey

I never know which way to go with details - when i spend 10 minutes writing a details question it’s usually a really simple solution!

I have done it this way in the end:


WHERE ISNULL(LinkContacts.ContactID,0) = COALESCE(@contactID, ISNULL(LinkContacts.ContactID,0)) AND
	Communications.ID = COALESCE(@communicationID, Communications.ID) AND
	((ISNULL(Communications.AddedBy,0) = COALESCE(@userID, ISNULL(Communications.AddedBy,0))) OR (ISNULL(LinkContacts.AddedBy,0) = COALESCE(@userID, ISNULL(Communications.AddedBy,0))))

cheers for all your help (and patients!) :wink:

thanks :slight_smile:

why is that your WHERE clause?

i might need you to explain in words what you really want, and where those variables come from…

That’s the problem - it is null! Is there no way around this then?

nothing is equal to NULL (not even another NULL)

change this –

WHERE LinkContact.ContactID = NULL

to this –

WHERE LinkContact.ContactID IS NULL

:slight_smile:

don’t worry about it, everybody does it, and i just simply answer the question as asked – it’s not my problem if the answer can’t be reverse engineered, is it :wink:

you would do it just like that

if @contactID is null, then the COALESCE expression will return LinkContact.ContactID, which is guaranteed to equal itself

unless, of course, it’s null

:smiley:

OK - i over simplified! How would i do this:


WHERE LinkContact.ContactID = COALESCE(@ContactID, LinkContact.ContactID)

if @contactID is null?

cheers

That works, however, my WHERE clause is:


WHERE Contacts.ID = COALESCE(@contactID, ID) AND
	Comms.ID = COALESCE(@communicationID, Comms.ID) AND
	((Comms.AddedBy = COALESCE(@userID, Comms.AddedBy)) OR (LinkContacts.AddedBy = COALESCE(@userID, LinkContacts.AddedBy)))

Because the LinkContacts.AddedBy and Contacts.ID will be null for any comm has not been linked to any contacts - these comms then fail to be selected.

thank you

that detailed explanation should have been given at the beginning

:slight_smile:

okay, to reach a solution, consider this: the nice thing about a stored proc is that you get to use IF/ELSE and can tailor three different queries, only one of which will ever get executed, based on the parameters passed in

go ahead, try it :slight_smile:

contact table:


CREATE TABLE [dbo].[Contacts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](20) NOT NULL,
	[Forename] [nvarchar](50) NOT NULL,
	[Surname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Contacts data:


id          title      forename   surname  
33	Mr	Albert	Steptoe
34	Mr	Steven	Jones
37	Brigadier	Don	Jonson
46	Miss	Jane	Doe

Comms Table:


CREATE TABLE [dbo].[Communications](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Details] [nvarchar](max) NULL,
	[AddedBy] [int] NOT NULL,
 CONSTRAINT [PK_ContactAudit] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Comms Data:


id           details                 AddedBy
39	Hello mum!	8330
40	Quick chat	8330
41	Another message	8330
42	Some information	8330
43	A message	8330

LinkContacts table:


CREATE TABLE [dbo].[Link_ContactCommunications](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ContactID] [int] NOT NULL,
	[CommunicationID] [int] NOT NULL,
	[AddedBy] [int] NOT NULL,
 CONSTRAINT [PK_Link_ContactCommunications] PRIMARY KEY CLUSTERED 
(
	[ContactID] ASC,
	[CommunicationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

LinkTable data:


id         contactid  commid    addedBy
72	33	39	8330
70	34	39	8330
79	46	40	8330
74	33	41	8330
76	34	41	8330
78	33	42	8330
80	34	42	8330
77	37	42	8330

as you can see communication 43 has not been linked to any user, but if i search for that communication by ID or by all comms added by 8330 i wish to see it with the link/contact data as null.

cheers

the three tables contain contacts, communications and a link table to link a communication to severl contacts (using contact id and communication id)

The procedure accepts 3 params, contactID, CommunicationID and userID - 2 of these will be null. This should bring back 1 of the following:

1, all contacts by contactID if there have been an communications with them (if not - no rows return)
2, all communication by communication ID and any contacts who recieved it - if no-one recieved it then i still want to return the communication to show this - this is the bit that doesn’t work
3, all communication either added by or linked by a user (by userID)


WHERE Contacts.ID = COALESCE(@contactID, ID) AND
	Comms.ID = COALESCE(@communicationID, Comms.ID) AND
	((Comms.AddedBy = COALESCE(@userID, Comms.AddedBy)) OR (LinkContacts.AddedBy = COALESCE(@userID, LinkContacts.AddedBy)))

any ideas? :slight_smile:

SELECT Communications.ID
     , Communications.Details
     , Link_ContactCommunications.ContactID
     , Link_ContactCommunications.CommunicationID
  FROM Communications
LEFT OUTER
  JOIN Link_ContactCommunications
    ON Link_ContactCommunications.CommunicationID = Communications.ID
 WHERE Communications.ID = 43
    OR Communications.AddedBy = 8330

This is all part of a procedure. The procedure can be called in 3 ways:

1: return all communication for a contact
@contactID=25
@communicationID=NULL
@userID=NULL

2: return a communication and all contacts linked to it (including communication with no linked contacts)
@contactID=NULL
@communicationID=45
@userID=NULL

3: return all communication added or linked by a user (including communication with no linked contacts)
@contactID=NULL
@communicationID=NULL
@userID=8330

Rather than have 3 seperate procedures to do this i am trying to write one hence the use of COALESCE.

before i look at a query, i always make sure i understand the data

could you do a SHOW CREATE TABLE, please

and then dump several sample rows, enough to demonstrate the various scenarios that you described

depends on what you’re trying to accomplish, which you neglected to mention :slight_smile: