CREATE TABLE myTable99 (col1 int)
GO

DECLARE @x int
SELECT @x = -1
SELECT * FROM myTable99 WHERE col1 = CASE WHEN @x = -1 THEN col1 ELSE @x END
GO

DROP TABLE myTable99
GO

CREATE TABLE tblContacts (
	contactID int IDENTITY (10001, 1) NOT NULL ,
	
	CONSTRAINT PK_tblContacts PRIMARY KEY  CLUSTERED 
	(
		contactID
	) WITH  FILLFACTOR = 90  ON PRIMARY ,
	
) ON PRIMARY

CREATE TABLE tblContactTypes (
	contactTypeID int IDENTITY (10001, 1) NOT NULL ,
	contactTypeDesc nvarchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT PK_tblContactTypes PRIMARY KEY  CLUSTERED 
	(
		contactTypeID
	) WITH  FILLFACTOR = 90  ON PRIMARY 
) ON PRIMARY

CREATE TABLE tblContactsContactTypes (
	contactID int NOT NULL ,
	contactTypeID int NOT NULL ,
	CONSTRAINT PK_tblContactsContactTypes PRIMARY KEY  CLUSTERED 
	(
		contactID,
		contactTypeID
	) WITH  FILLFACTOR = 90  ON PRIMARY ,
	CONSTRAINT FK_tblContactsContactTypes_tblContacts FOREIGN KEY 
	(
		contactID
	) REFERENCES tblContacts (
		contactID
	) ON DELETE CASCADE ,
	CONSTRAINT FK_tblContactsContactTypes_tblContactTypes FOREIGN KEY 
	(
		contactTypeID
	) REFERENCES tblContactTypes (
		contactTypeID
	) ON DELETE CASCADE 
) ON PRIMARY

CREATE TABLE tblDaysContacts (
	dayID int NOT NULL ,
	contactID int NOT NULL ,
	primaryContact bit NOT NULL CONSTRAINT DF_tblDaysContacts_primaryContact DEFAULT (0),
	CONSTRAINT PK_tblDaysContacts PRIMARY KEY  CLUSTERED 
	(
		dayID,
		contactID
	) WITH  FILLFACTOR = 90  ON PRIMARY ,
	CONSTRAINT FK_tblDaysContacts_tblContacts FOREIGN KEY 
	(
		contactID
	) REFERENCES tblContacts (
		contactID
	) ON DELETE CASCADE ,
) ON PRIMARY

SELECT tblContactsContactTypes.contactID
FROM tblDaysContacts RIGHT OUTER JOINtblContacts ON tblDaysContacts.contactID = tblContacts.contactID LEFT OUTER JOIN  tblContactTypes RIGHT OUTER JOIN tblContactsContactTypes ON tblContactTypes.contactTypeID=tblContactsContactTypes.contactTypeID ON tblContacts.contactID=tblContactsContactTypes.contactID
WHERE (tblDaysContacts.dayID = @dayID) AND tblContactTypes.contactTypeID= CASE WHEN @ctID=-1 THEN tblContactTypes.contactTypeID ELSE @ctID END)

     SELECT cct.contactID
       FROM tblDaysContacts dc
 RIGHT JOIN tblContacts c
	 ON dc.contactID = c.contactID 
  LEFT JOIN tblContactTypes t
 RIGHT JOIN tblContactsContactTypes cct
	 ON t.contactTypeID=cct.contactTypeID 
	 ON c.contactID=cct.contactID
      WHERE dc.dayID = @dayId 
	AND t.contactTypeID= CASE WHEN @ctID=-1 THEN t.contactTypeID ELSE @ctID END

quote:

---

So the problem becomes that

I cant set tblContactTypes.contactTypeID=tblContactTypes.contactTypeID

if @ctID is -1

because

if the Contact is not related to any contactID

they will not be returned by the query.

Does this make any more sense?

---

ISNULL(t.contactTypeID,'')= CASE WHEN @ctID=-1 THEN ISNULL(t.contactTypeID,'') ELSE @ctID END