Solved

mysql join where second table condition does not exist

Posted on 2009-05-16
5
309 Views
Last Modified: 2013-12-12
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)
0
Comment
Question by:thewebwench
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24404223

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
 
LVL 14

Accepted Solution

by:
racek earned 400 total points
ID: 24404231

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
 

Author Comment

by:thewebwench
ID: 24404276
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
 

Author Comment

by:thewebwench
ID: 24404293
never mind, I've gotten it to match; THANKS
0
 

Author Comment

by:thewebwench
ID: 24404297
I accidentally marked the wrong solution; it should have been the first one.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now