Remove items from query based on another table

[lunac]

Ok, here's an odd situation that I just can't figure out.

 

I have to tables. A directory table and a dues table. The dues table contains a payment for any member that has paid with the id of their associated record in the directory tables. IE. payment 2007 for id 2 -- id 2 is Bob.

 

If I do a join function I can easily find all the members that have paid for the year.

 

NOW....

how do I find the members that haven't paid? I know it's something like if the id doesn't exist in dues then show it as a record from directory. But how dow I do that exactly?

 

tables:

 

dues --> dues_id, directory_id, amount, year, check_num

directory --> directory_id, fname, lname

 

 

 

[lunac]

Ok....

 

I figured it out, but here's the answer for anyone else getting stuck on this issue

 

------- please not mysqlSelect is a custom function for rewiting a query statement ----------------

$a = mysqlSelect($_tables['directory']);

while($k = mysql_fetch_assoc($a)){
		// check members dues 
		$z = mysqlSelect($_tables['member_dues'], "dues_id", "WHERE directory_id = " . $k['directory_id']);
		$count = mysql_num_rows($z);
		if($count == 0){
			echo $x . $k['lname'] . "<br />";

                  }
}

 

 

[Barand]

or

 

$sql = "SELECT dir.fname, dir.lname
        FROM directory dir
        LEFT JOIN dues du ON dir.directory_id = du.directory_id AND du.year = YEAR(NOW())
        WHERE du.directory_id IS NULL";