Comparing data from two tables

[Ravinos]

How would you go about taking data from two tables and then displaying the results that are different from the 2nd table.

example:

Table 1 (INVITED)
Name
Bob
Robert

Table 2 (RSVP)
Name
Bob

So I invited both Bob and Robert, only Bob has RSVP'd and Robert still needs to. I want a result to tell me "Robert needs to RSVP" because Robert doesn't exist in the 2nd table.

I hope that makes sense. I know I can just make a table that has a toggle for INVITED and RSVP but these have to be two separate tables.

[KunoNoOni]

You could compare each name in the invited table to all the names in the rsvp table and if the name is not there print out that name.


-KunoNoOni

[Ravinos]

That is what I'm trying to do

[Zyviel]

I believe the sql would look something like this possibly:

select * from table1 as t1
left join table2 as t2
on t1.name = t2.name
where t2.name is null

[Chris]

I'm not an expert in SQL bu I think this could be a simple solution, haven't tried it myself:

Code: Select all

$query = mysql_query("SELECT `name` FROM `table1` WHERE NOT EXISTS (SELECT `name` FROM `table2`)");
while ( $array = mysql_fetch_assoc($query) )
{
    echo $array['name'] . "<br />\n";
}

 

[Jackolantern]

Both of those illustrate viable techniques (of course I am not an SQL master myself, so I would have to have a db setup to test them, but the techniques are both valid). Zyviel used an "Inner-Join" and Chris used a "Subquery".

[Ravinos]

The sub query route seems to have worked the best for me. I'm surprised I haven't needed this for my game yet. This was actually for a project for a friends website I'm designing for him. It's an event hosting website.

Thanks all for the help.