Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Where should I use outer joins?

by athomason (Curate)
on May 09, 2000 at 10:55 UTC ( [id://10718]=perlquestion: print w/replies, xml ) Need Help??

athomason has asked for the wisdom of the Perl Monks concerning the following question: (database programming)

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.

Replies are listed 'Best First'.
Re: Where should I use outer joins?
by Russ (Deacon) on May 09, 2000 at 11:35 UTC
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://10718]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others about the Monastery: (5)
As of 2024-03-29 13:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found