Select ContactID if field doesn't have a specific value

[gertie2008]

Lets say I have 2 tables:
"tblContactDeta ils" with fields "ContactID" and "UserName" and
"tblCommTra ck" with fields "CommTrackI D", "ContactID" , "CommType", "DateContac ted"
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=2 008-03-13
[CODE=mysql] SELECT DISTINCT(d.Cont actID), d.UserName FROM tblContactDetai ls AS d Left Join tblCommTrack AS c ON d.ContactID = c.ContactID WHERE (c.CommTrackID IS NULL OR c.CommType=2 OR (c.DateContacte d <> 2008-03-13) ORDER By d.UserName[/CODE]
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=2 008-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:
[code=mysql]
CREATE DEFINER=`root`@ `localhost` PROCEDURE `getSpecNotCont acted`(
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=iCommT ype;


SELECT DISTINCT(d.Cont actID), d.UserName, d.TelCell
FROM tblContactDetai ls AS d Left Join temp AS t ON d.ContactID = t.ContactID
WHERE t.CommTrackID IS NULL
AND d.CoachedByCont actID = CoachID
AND d.ContactID <> CoachID ORDER By d.UserName;
END[/code]

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