Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Where should I use outer joins?

by athomason (Curate)
on May 09, 2000 at 10:55 UTC ( #10718=categorized question: print w/replies, xml ) Need Help??
Contributed by athomason on May 09, 2000 at 10:55 UTC
Q&A  > 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?

Answer: Where should I use outer joins?
contributed by 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) | | | | | +-------+------------------+------+-----+---------+----------------+
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 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.


Please (register and) log in if you wish to add an answer

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others surveying the Monastery: (7)
    As of 2019-09-23 15:23 GMT
    Find Nodes?
      Voting Booth?
      The room is dark, and your next move is ...

      Results (280 votes). Check out past polls.