Solved

mysql join where second table condition does not exist

Posted on 2009-05-16
5
308 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 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

11 Experts available now in Live!

Get 1:1 Help Now