Select ContactID if field doesn't have a specific value

[gertie2008]

Lets say I have 2 tables:
"tblContactDetails" with fields "ContactID" and "UserName" and
"tblCommTrack" with fields "CommTrackID", "ContactID", "CommType", "DateContacted"
Case one: tblCommTrack has one row with values: 1,10,1, 2008-03-13 respectively. The following query works, because it doesn't return any rows for the indivudual with ContactID=10 WHERE CommType=1 AND DateContacted=2008-03-13

 SELECT DISTINCT(d.ContactID), d.UserName FROM tblContactDetails AS d Left Join tblCommTrack AS c ON d.ContactID = c.ContactID WHERE (c.CommTrackID IS NULL OR c.CommType=2 OR (c.DateContacted <> 2008-03-13) ORDER By d.UserName

Case two: tblCommTrack has two rows with values: 1,10,1, 2008-03-13 and 1,10,2, 2008-03-13 Now the query doesn't work, because a record for individual with ContactID=10 is returned, and I don't want that. I only want to return records for individuals who don't have a value of 1 in the CommType field AND if the record with CommType=1 has DateContacted=2008-03-13.

[gertie2008]

Problem solved:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getSpecNotContacted`(
IN CoachID INT,
IN iCommType INT,
IN fromDate DATE,
IN toDate DATE
)
BEGIN
drop table IF EXISTS temp;
CREATE TEMPORARY TABLE temp (CommTrackID INT, ContactID INT);
 
   INSERT INTO temp
   SELECT CommTrackID, ContactID FROM tblCommTrack
   WHERE DateContacted >= fromDate
   AND DateContacted <= toDate
   AND CommType=iCommType;
 
 
SELECT DISTINCT(d.ContactID), d.UserName, d.TelCell
FROM tblContactDetails AS d Left Join temp AS t ON d.ContactID = t.ContactID
WHERE t.CommTrackID IS NULL
AND d.CoachedByContactID = CoachID
AND d.ContactID <> CoachID ORDER By d.UserName;
END

[ronverdonk]

Congrats on finding the solution.

However, the moderators cannot continue to change your posts for enclosing code within code tags!!

So enclose any code within the proper code tags. See the Posting Guidelines on how to do that.

MODERATOR

[gertie2008]

Problem solved:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getSpecNotContacted`(
IN CoachID INT,
IN iCommType INT,
IN fromDate DATE,
IN toDate DATE
)
BEGIN
drop table IF EXISTS temp;
CREATE TEMPORARY TABLE temp (CommTrackID INT, ContactID INT);
 
   INSERT INTO temp
   SELECT CommTrackID, ContactID FROM tblCommTrack
   WHERE DateContacted >= fromDate
   AND DateContacted <= toDate
   AND CommType=iCommType;
 
 
SELECT DISTINCT(d.ContactID), d.UserName, d.TelCell
FROM tblContactDetails AS d Left Join temp AS t ON d.ContactID = t.ContactID
WHERE t.CommTrackID IS NULL
AND d.CoachedByContactID = CoachID
AND d.ContactID <> CoachID ORDER By d.UserName;
END

This is what I came up with. Does anyone know a shorter way? (Thanks ronverdonk for helping me to post better.)