SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More advanced SQL than I've done... blah!

    I haven't used unions or left/right joins in the past and think I've finally stumbled across the need for, well, something.

    Say I have two tables:

    events {
    id int
    source varchar(255)
    headline text
    description text
    etc ... (rest irrelevant)
    }

    and

    userpicks {
    id int
    source varchar(255)
    username text
    ...
    }

    Basically, there's a one-to-many relationship between events and picks. ID and Source work as primary keys. So, if I want to know how many votes an event has, it's basically:
    SELECT count(*) FROM userpicks WHERE id="bar" AND source=999
    What I want to do is this:

    return all columns from events, plus a column with the vote total for said event

    I tried:
    SELECT events.eventid, events.source, events.begin, events.end, count(events.eventid) as picks FROM events, userpicks WHERE events.source=userpicks.source AND events.eventid=userpicks.eventid AND events.source=999 AND events.eventid='bar' GROUP BY eventid
    That query worked nicely -- except it returns an empty resultset if there are NO votes. I need a result even if there are no votes (votes can be blank or 0, I don't care which). I'm pretty sure this calls for more advanced SQL than I've done.

    Suggestions?

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    use:
    Code:
    SELECT   events.eventid
             ,events.source
             ,events.`begin`
             ,events.`end`
             ,COUNT(events.eventid) AS picks
    FROM     events
             RIGHT JOIN userpicks
               ON events.source = userpicks.source
                  AND events.eventid = userpicks.eventid
    WHERE    events.source = 999
             AND events.eventid = 'bar'
    GROUP BY eventid;
    Chagh

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select events.id
         , events.source
         , events.headline
         , events.description 
         , ( select count(*)
               from userpicks
              where id = events.id
                and source = events.source ) as picks
      from events
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •