Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Where should I use outer joins?

( #10718=categorized question: print w/ replies, xml ) Need Help??
Contributed by athomason on May 09, 2000 at 10:55 UTC
Q&A  > database programming


Description:

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) | | | | | +-------+------------------+------+-----+---------+----------------+
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

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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (4)
    As of 2014-12-22 06:29 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (111 votes), past polls