mysql join where second table condition does not exist

I have a table contact with key i_contact
I have a table schedule with a field i_contact
I need all contact.i_contact ids that meet certain criteria AND where they do not have a record in schedule with certain criteria.  There may be records in schedule, but I need those that do not have certain characteristics.

For example,
SELECT c.i_contact, c.i_broker, c.fname, c.lname, c.email, c.hphone, c.cphone, c.state, c.referral_source, c.fico, c.amort, c.profile_completed, c.paid, c.created, c.is_customer, c.deleted from contact
INNER JOIN primary_home as p on p.i_contact=c.i_contact AND p.isLiquid=1
LEFT OUTER JOIN schedule as s on s.i_contact=c.i_contact
AND (s.task Like '%meeting%' or s.task Like '%go to%' or s.task Like '%goto%' or s.task Like '%online%')
WHERE c.deleted=0
This gives me (I think) the data on contacts who's primary_home field isLiquid=1 and they have a record in schedule like the parameters listed above.  What I need is 2 things:
1.  am I right with the first query
2.  how do I reverse that and have it give me only those contacts who'se primary_home field isLiquid=1 who may or may not have records in schedule, but who do NOT have any record in schedule where the task is like (meeting, online, goto, go to)
thewebwenchAsked:
Who is Participating?
 
racekConnect With a Mentor Commented:

SELECT c.i_contact, c.i_broker, c.fname, c.lname, c.email, c.hphone, c.cphone, c.state, c.referral_source, c.fico, c.amort, c.profile_completed, c.paid, c.created, c.is_customer, c.deleted 
from contact c
INNER JOIN primary_home as p on p.i_contact=c.i_contact AND p.isLiquid=1
LEFT OUTER JOIN schedule as s on s.i_contact=c.i_contact
WHERE c.deleted=0 
AND NOT (s.task Like '%meeting%' or s.task Like '%go to%' or s.task Like '%goto%' or s.task Like '%online%')

Open in new window

0
 
racekCommented:

SELECT c.i_contact, c.i_broker, c.fname, c.lname, c.email, c.hphone, c.cphone, c.state, c.referral_source, c.fico, c.amort, c.profile_completed, c.paid, c.created, c.is_customer, c.deleted 
from contact c
INNER JOIN primary_home as p on p.i_contact=c.i_contact AND p.isLiquid=1
LEFT OUTER JOIN schedule as s on s.i_contact=c.i_contact
AND (s.task Like '%meeting%' or s.task Like '%go to%' or s.task Like '%goto%' or s.task Like '%online%')
WHERE c.deleted=0 
and s.i_contact IS NULL

Open in new window

0
 
thewebwenchAuthor Commented:
what's the difference between the two?  I think the first one is more right.  I get about expected results (not exact, but about) where with the second one I get hundreds more results -- I get all contacts with rows in schedule where those rows don't equal "online, meeting, etc."

I should be getting 45 results (unless my first query is not right)
I'm getting 42 with your query
0
 
thewebwenchAuthor Commented:
never mind, I've gotten it to match; THANKS
0
 
thewebwenchAuthor Commented:
I accidentally marked the wrong solution; it should have been the first one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.