• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL for records that don't exist

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
axessJosh
Asked:
axessJosh
  • 2
1 Solution
 
Juan OcasioApplication DeveloperCommented:
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
 
axessJoshAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
axessJoshAuthor Commented:
I had to remove a ")" at the end of the SQL statement but other than that it worked great.  Thanks.
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now