4 Replies Latest reply on Apr 20, 2006 10:33 AM by starfox00

    Advanced SQL Query Help

    wjs
      Hello everyone,

      I'm currently working on a music publishing project. This project is accepting song (sheet music) submissions for inclusion in a book. We have a committee of four members that will be "voting" on whether or not to include the songs in this book.

      Where I'm getting stuck is this: I am creating a page where the committee members can see a list of the songs that have been submitted. A member can look at the song's sheet music and place their vote via links on the page. They can only vote once per song.

      What I need help on is writing a query that will pull up all of the songs listed in the database that have either no votes, or one, two, or three votes (not four votes). The problem is in writing the query that gets songs that have either NONE or less than FOUR. If I was only looking for songs with some votes I could easily do an inner join to get my results, but because I'm looking for songs with no votes or a few in an associated table, I'm having trouble.

      For simplicity sake, here are the table's functions:

      Song Table
      Song ID
      Song Title
      Compose Name
      etc...

      Vote Table
      Song ID
      Approval Member Name
      Vote (yes, no)

      How would I set up the query to pull the information I need? I'm completely stuck and could definitely use the help.

      TIA,
      Josh
        • 1. Re: Advanced SQL Query Help
          rmorgan Level 1
          If I understand correctly what you are looking for:

          select songtable.songtitle
          from songtable
          inner join votetable
          on songtable.songid = votetable.songid
          where vote = null
          or vote < 4

          • 2. Re: Advanced SQL Query Help
            wjs Level 1
            I don't think that's going to work. The voting table looks like this:

            SongID
            VoterName
            yesNo


            Data would be like this:

            1
            Josh
            yes

            1
            Mark
            yes

            1
            Joe
            no

            3
            Josh
            no

            3
            Steve
            yes

            That kind of thing. If there is no entry for a particular songID in the voting table, the inner join would eliminate it from showing a result.

            • 3. Advanced SQL Query Help
              Steven
              You need a left join:

              SELECT song.title
              FROM song LEFT JOIN vote ON song.ID = vote.songid

              This will show ALL records on the song side where id = vote.songid

              • 4. Advanced SQL Query Help
                starfox00
                how about something like this?

                ========================================================================================== ======
                SELECT Songs.SongTitle,Count(Votes.yesNo) AS CountOfVote
                FROM Songs LEFT JOIN Votes ON Songs.SongID = Votes.SongID
                GROUP BY Songs.SongTitle
                HAVING COUNT(Votes.yesNo) < 4;
                ========================================================================================== ======

                you might have to modify some column names but this format should give you what you seek.

                let me know how this works.