Where should I use outer joins?

[athomason]

Some background: I'm implementing a voting system using MySQL, with multiple polls being voted upon at any time. I have two tables: one called 'polls', which has an 'id' auto_inc field and a 'poll' string. Votes are held in the 'poll_votes' table; each vote record has an auto_inc 'id', a 'poll_id' int recording which poll the vote is for, and an 'address' string for the IP of the voter.

Here's the problem. I want to retrieve a list of those polls which the IP address in question has *not* voted upon. I can't figure out how to do this purely in SQL. It would be easy to 1) get a list of polls, and then 2) for each poll, check if the user has voted on that poll. But I'd like to just fire off a single SELECT and receive a list of 'poll' strings to do CGI junk with. I've never used joins before, so I'm having difficult wrapping my mind around the problem. Is it possible to do this with a single query?

Originally posted as a Categorized Question.

[Russ]

Given the following tables:

mysql> desc poll;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned |      | PRI | 0       | auto_increment |
| poll  | varchar(50)      |      |     |         |                |
+-------+------------------+------+-----+---------+----------------+

and

mysql> desc poll_votes;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned |      | PRI | 0       | auto_increment |
| poll_id | int(10) unsigned |      |     | 0       |                |
| address | varchar(50)      |      |     |         |                |
+---------+------------------+------+-----+---------+----------------+

with data like:

mysql> select * from poll;
+----+--------+
| id | poll   |
+----+--------+
|  1 | perl   |
|  2 | python |
|  3 | C      |
|  4 | C++    |
+----+--------+
mysql> select * from poll_votes;
+----+---------+----------+
| id | poll_id | address  |
+----+---------+----------+
|  1 |       1 | junk     |
|  2 |       2 | junk     |
|  3 |       2 | morejunk |
+----+---------+----------+

My first pass is this statement:

         select p.poll 
           from poll p 
left outer join poll_votes pv 
             on pv.poll_id=p.id and pv.address='junk' 
          where pv.poll_id is null;

It returns:

+------+
| poll |
+------+
| C    |
| C++  |
+------+

We do a left outer join (so we can see what we *didn't* get) on the address you want and have the 'where' clause give us what the join didn't find ('where pv.poll_id is null').
This should give you the polls for which this address has not voted.

Note: I assume you want to restrict votes to one vote per address per poll. You can easily accomplish this by removing id from poll_votes and making the primary key be both poll_id and address. This way, MySQL will enforce your policy for you.

Russ