469,125 Members | 1,825 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Select ContactID if field doesn't have a specific value

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
Expand|Select|Wrap|Line Numbers
  1.  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.
Mar 13 '08 #1
3 1283
Problem solved:
Expand|Select|Wrap|Line Numbers
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `getSpecNotContacted`(
  2. IN CoachID INT,
  3. IN iCommType INT,
  4. IN fromDate DATE,
  5. IN toDate DATE
  6. )
  7. BEGIN
  8. drop table IF EXISTS temp;
  9. CREATE TEMPORARY TABLE temp (CommTrackID INT, ContactID INT);
  10.  
  11.    INSERT INTO temp
  12.    SELECT CommTrackID, ContactID FROM tblCommTrack
  13.    WHERE DateContacted >= fromDate
  14.    AND DateContacted <= toDate
  15.    AND CommType=iCommType;
  16.  
  17.  
  18. SELECT DISTINCT(d.ContactID), d.UserName, d.TelCell
  19. FROM tblContactDetails AS d Left Join temp AS t ON d.ContactID = t.ContactID
  20. WHERE t.CommTrackID IS NULL
  21. AND d.CoachedByContactID = CoachID
  22. AND d.ContactID <> CoachID ORDER By d.UserName;
  23. END
Mar 14 '08 #2
ronverdonk
4,258 Expert 4TB
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
Mar 14 '08 #3
Problem solved:
Expand|Select|Wrap|Line Numbers
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `getSpecNotContacted`(
  2. IN CoachID INT,
  3. IN iCommType INT,
  4. IN fromDate DATE,
  5. IN toDate DATE
  6. )
  7. BEGIN
  8. drop table IF EXISTS temp;
  9. CREATE TEMPORARY TABLE temp (CommTrackID INT, ContactID INT);
  10.  
  11.    INSERT INTO temp
  12.    SELECT CommTrackID, ContactID FROM tblCommTrack
  13.    WHERE DateContacted >= fromDate
  14.    AND DateContacted <= toDate
  15.    AND CommType=iCommType;
  16.  
  17.  
  18. SELECT DISTINCT(d.ContactID), d.UserName, d.TelCell
  19. FROM tblContactDetails AS d Left Join temp AS t ON d.ContactID = t.ContactID
  20. WHERE t.CommTrackID IS NULL
  21. AND d.CoachedByContactID = CoachID
  22. AND d.ContactID <> CoachID ORDER By d.UserName;
  23. END
This is what I came up with. Does anyone know a shorter way? (Thanks ronverdonk for helping me to post better.)
Mar 17 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Martin Feuersteiner | last post: by
3 posts views Thread by Ian T | last post: by
3 posts views Thread by Tcs | last post: by
6 posts views Thread by iam247 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.