Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL for records that don't exist

Posted on 2011-04-28
4
Medium Priority
?
330 Views
Last Modified: 2012-06-27
A final piece to my puzzle.  Been trying different variations most of the afternoon.  I need to develop a query for records that don't exist.  Essentially, I need to display the church names of churches that are in my church table but do not have records existing for dates selected by the user.

I tried using "NOT EXISTS" but could not get it to pass any results.  You continued help is much appreciated.

Here is my attempt.  
$dateVar_rs_notexists = "-1";
if (isset($_POST['dateChoice'])) {
  $dateVar_rs_notexists = $_POST['dateChoice'];
}
$yearVar_rs_notexists = "-1";
if (isset($_POST['yearChoice'])) {
  $yearVar_rs_notexists = $_POST['yearChoice'];
}
mysql_select_db($database_con_db_local, $con_db_local);
$query_rs_notexists = sprintf("SELECT tblchurch.churchName,tblchurchinfo.dateID, tblchurchinfo.yearID FROM tblchurch, tblchurchInfo WHERE NOT EXISTS (SELECT tblchurchinfo.churchID, tblchurch.churchID FROM tblchurchinfo, tblchurch WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.yearID = %s AND tblchurchinfo.churchID = tblchurch.churchID)", GetSQLValueString($dateVar_rs_notexists, "int"),GetSQLValueString($yearVar_rs_notexists, "int"));
$rs_notexists = mysql_query($query_rs_notexists, $con_db_local) or die(mysql_error());
$row_rs_notexists = mysql_fetch_assoc($rs_notexists);
$totalRows_rs_notexists = mysql_num_rows($rs_notexists);
?>

Open in new window

0
Comment
Question by:axessJosh
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35487653
Why not try NOT IN as in:

SELECT tblchurch.churchName,tblchurchinfo.dateID, tblchurchinfo.yearID FROM tblchurch, tblchurchInfo WHERE NOT IN (SELECT tblchurchinfo.churchID, tblchurch.churchID FROM tblchurchinfo, tblchurch WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.yearID = %s AND tblchurchinfo.churchID = tblchurch.churchID)", GetSQLValueString($dateVar_rs_notexists, "int"),GetSQLValueString($yearVar_rs_notexists, "int"));
$rs_notexists = mysql_query($query_rs_notexists, $con_db_local) or die(mysql_error());
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35487687
Browser didn't like that.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (SELECT tblchurchinfo.churchID, tblchurch.churchID FROM tblchurchinfo, tblchu' at line 1
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35488041
try this.
$dateVar_rs_notexists = "-1";
if (isset($_POST['dateChoice'])) {
  $dateVar_rs_notexists = $_POST['dateChoice'];
}
$yearVar_rs_notexists = "-1";
if (isset($_POST['yearChoice'])) {
  $yearVar_rs_notexists = $_POST['yearChoice'];
}
mysql_select_db($database_con_db_local, $con_db_local);
$query_rs_notexists = sprintf("SELECT tblchurch.churchName,tblchurchinfo.dateID, tblchurchinfo.yearID FROM tblchurch left join tblchurchInfo  on tblchurchinfo.dateID = %s AND tblchurchinfo.yearID = %s AND tblchurchinfo.churchID = tblchurch.churchID where tblchurchinfo.churchID is null)", GetSQLValueString($dateVar_rs_notexists, "int"),GetSQLValueString($yearVar_rs_notexists, "int"));
$rs_notexists = mysql_query($query_rs_notexists, $con_db_local) or die(mysql_error());
$row_rs_notexists = mysql_fetch_assoc($rs_notexists);
$totalRows_rs_notexists = mysql_num_rows($rs_notexists);
?>

Open in new window

0
 
LVL 2

Author Closing Comment

by:axessJosh
ID: 35492372
I had to remove a ")" at the end of the SQL statement but other than that it worked great.  Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 22 hours left to enroll

810 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