Ok, I am having a little trouble grabbing the exact data I want from my database. This is the scenario:

I have 3 tables, a member table (memberID, firstname, lastname), an eventType table (eventTypeID, eventType), and an attendance table (attendanceID, memberID, eventTypeID, date). Of course these are simplified, but you get the point. The point of these two tables is to track the attendance of people in the database at certain types of events, be it a concert, basketball game, hockey game...

So now in a web application I may want to search for all the people in the database that have NOT attended a certain event type (concerts).

How do I grab that information? All of the joins I have tried give me both the people who have attended the eventype and those who have not. Is there a way to ONLY pull from the database those who have not attended, or must I get all of them and then separate out the data. If the latter is the case, how would I do that?

Any help would be greatly appreciated.

I would join the member table and the attendence table together in one sql query and use a clause WHERE attendence = whatever the indentifier is.

for more information on table joins you can http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/

Try this:
$eventype = 'concert';

$sql = "SELECT m.memberID, m.firstname, m.lastname, a.memberID, a.eventTypeID, e.eventTypeID, e.eventType FROM member m, attendence a, eventType e WHERE e.eventType = '$eventype '";

and if you do a;

do { echo $row['firstname'] , $row['lastname'] } while ($row = mysql_fetch_assoc($result));

It should display all then names that event type is concert

I believe that is what left-joins are for. I have not used them because I usually code around them but if anyone knows I would also be interested to hear how they work and if they would work for this application.

Thank you for the replies, however it is still not working. I know how to use a left join, and the purpose. One of the variations that I have tried:

$query = "SELECT * FROM members LEFT JOIN attendance USING (memberID) WHERE attendance.attendanceID is null ";

I thought this one was working for a while, however it just pulls anyone who has not attended ANY event, not just the ones who havent attended a specific event.


Anyway, thanks for the input...keep it commin

Finally got it to work. I ended up having to grab all the members in the database, then separately grab all the people who HAVE attended the certain event. I then grabbed the memberID from the attendance array and matched it against the members array. If the memberID matched the member array I unset the element. This was the code:

PHP Code:

$q = "SELECT * FROM members";
$r = mysql_query($q);
$array = array();
    while ($rows = mysql_fetch_array($r)) {
        array_push($array, $rows);
    }
$searchResults = $array;



        $query = "SELECT * FROM attendance WHERE eventTypeID = '$this->notYetAttended'";
        $result = mysql_query($query);
        
        $array1 = array();
        while ($rows = mysql_fetch_array($result)) {
            array_push($array1, $rows);
        }

        
        for ($i=0; $i < mysql_num_rows($result); $i++) {
            $member = "memberID".$i;
            $$member = $array1[$i]['memberID'];
        
            for ($j=0; $j < mysql_num_rows($r); $j++) {
                if ($searchResults[$j]['memberID'] == $$member)
                    unset($searchResults[$j]);
            }

        }
        // After unsetting the elements in the array, the keys maynot be in continuous order.  THis section essentially resets the keys into continuous chronological order 
        $count = count($searchResults);
        $results = array_pad($searchResults, -(1 + $count), array(1, 2, 3, 4));
        //print_r($this->searchResults);
        array_shift($results);
        $searchResults = $results;

    
    
        $info = "<searchResults>";

    while ($searchResults) {
        $info .= "\n  <node memberID=\"".$searchResults[0]['memberID']."\" firstName=\"".$searchResults[0]['firstName']."\" lastName=\"".$searchResults[0]['lastName']."\" />";
        array_shift($searchResults);
    }
    
    $info .= "\n</searchResults>";
    print $info; 


Sorry if this is hard to read and (may) have errors. I cut and pasted this from various files. I hope this helps someone...took me way too many hours to figure this out.