Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Question Unanswered: Need some help with a query for my fiance

    Hi all,

    I need some help with a query I've been trying to put together. I've got an Access DB with a web front end I built for my fiance so she can track the songs she likes to play on RockBand. I know, it sounds silly, but she really likes it and I'd like to make a few improvements for her. The DB is very simple, it just has one table with the following columns:

    ID (key - integer)
    Band (name of the band)
    SongName (name of the song)
    SongLevel (song difficulty defined by the game)
    Difficulty (difficulty she played it on Easy, Medium, Hard, Expert)
    Performance (how she did on the song in %)
    Stars (# of stars she achieved on the song)
    Fun (fun factor integer - so she can track which songs she really likes/hates to play)
    Notes (misc notes she can attach to each song)

    The complexity here is that some songs exist in the DB more than once, for various difficulty levels she's played the song at. For example, there could be a row for song XYZ where she played it at Medium Difficulty and scored a 100% in Performance. That same song could be in another row where she played it on Hard Difficulty and scored a 96% Performance. Even still, that same song could also be listed on Expert Difficulty with a 94% Performance in another row.

    What I'd like to be able to do is run a query that helps her find songs she can improve on. For example, one query would be:

    "Find all the songs she played on Hard Difficulty where her Performance was greater than 96% but if the song also exists in the table with Expert Difficulty, then don't include the song in the results"

    So basically, these results would contain a list of songs she played on Hard (not Expert) and scored greater than 96%. This would allow her to try those songs on Expert and try to improve her playing on the harder difficulty.

    I hope this makes sense. I couldn't figure out how to write a query to do this so I'd appreciate any advice.

    Thanks!

  2. #2
    Join Date
    May 2008
    Posts
    277
    Access's SQL is hardly to standard, but try this:

    Code:
    SELECT SongName
    FROM
        RockBand AS hard
        LEFT JOIN RockBand AS expert ON hard.SongName = expert.SongName
    WHERE
        hard.Difficulty = 'Hard'
        AND hard.Performance < 100
        AND expert.Difficulty <> 'Expert'
    For a generalized solution, you can specify your Difficulty levels as numbers (maybe by incorporating a look-up table): 1 = Easy, 2 = Medium, 3 = Hard, 4 = Expert.

    Code:
    SELECT SongName
    FROM
        RockBand AS desired_level
        LEFT JOIN RockBand AS max_level ON desired_level.SongName = max_level.SongName
    WHERE
        desired_level.Difficulty = 3
        AND desired_level.Performance < 100
        AND desired_level.Difficulty >= max_level.Difficulty
    By parameterizing the desired difficulty level, this query should work for any level of difficulty, returning all songs performed at less than 100% that have not been attempted at a higher level of difficulty.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The one issue I take with the above SQL is starting out a left join to turn back into an inner join. Why not just write as an inner join?
    Code:
    SELECT SongName
    FROM
        RockBand AS hard
        INNER JOIN RockBand AS expert 
           ON hard.SongName = expert.SongName
           AND expert.Difficulty <> 'Expert'
    WHERE
        hard.Difficulty = 'Hard'
        AND hard.Performance >= 96
    Dave

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by dav1mo View Post
    Why not just write as an inner join?
    I was thinking you'd lose songs that had only ever been accomplished at one difficulty level, but on second thought, your query should also work.
    Last edited by futurity; 06-11-10 at 14:15.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You would have lost the same songs with your original query as you had an AND predicate on the outer joined table in the WHERE clause, thus, as I mentioned, turning an OUTER join into an INNER join. The better performing way to have written it though would, probably, be using a NOT EXISTS. Something like:
    Code:
    SELECT SongName
    FROM
        RockBand AS hard
    WHERE hard.Difficulty = 'Hard'
        AND hard.Performance >= 96
        AND NOT EXISTS (select 1 from RockBand AS expert 
                                 where hard.SongName = expert.SongName
                                    AND expert.Difficulty = 'Expert')
    Dave

  6. #6
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by dav1mo View Post
    You would have lost the same songs with your original query as you had an AND predicate on the outer joined table in the WHERE clause
    Unless I'm mistaken, the song will, at the very least, always rejoin on itself, so it will never be lost. So an INNER JOIN will work, as you pointed out.

    The better performing way to have written it though would, probably, be using a NOT EXISTS.
    As long as we're having fun, how about an EXCEPT?

    Code:
    SELECT SongName
    FROM RockBand
    WHERE Difficulty = 'Hard' AND Performance < 100
    EXCEPT ALL
    SELECT SongName
    FROM RockBand
    WHERE Difficulty = 'Expert'
    Thus bringing up one of the things I don't like about SQL: 5000 different ways to do the same thing, and basically no way of knowing which is "best".
    Last edited by futurity; 06-11-10 at 14:58.

  7. #7
    Join Date
    Jun 2010
    Posts
    9
    First, thanks for the assistance fellas!

    Dav1mo -

    I tried your query using the INNER JOIN, but it appears Access 2007 doesn't support joins. It threw an error at me. I also tried your NOT EXISTS query, and it returned results, just not what I was expecting. It brought back songs that were listed only as hard, songs that were listed as hard and expert, and omitted other songs listed only as hard that it should have grabbed.


    futurity -

    I tried your EXCEPT ALL query but Access threw an error on the second SELECT clause saying there was something wrong with the syntax.

  8. #8
    Join Date
    May 2008
    Posts
    277
    As I mentioned, Access's support of standard SQL is quite poor. It does support INNER JOINs, but generally you need to throw in a bunch of random parantheses and brackets to get stuff to work. Here's an online SQL reference that might help.

    Try this:
    Code:
    SELECT SongName
    FROM RockBand AS hard
    WHERE 
        hard.Difficulty = 'Hard'
        AND hard.Performance < 100
        AND NOT EXISTS (
            SELECT 1 
            FROM RockBand AS expert
            WHERE 
                hard.SongName = expert.SongName
                AND expert.Difficulty = 'Expert')
    If not, paste the exact error message you get. Alternatively, you may want to try the Access forum; someone there might be more familiar with Access's dialect of SQL.

  9. #9
    Join Date
    Jun 2010
    Posts
    9
    Thanks Futurity

    That query returns the same mixed resultset. Strange.

    The error I get when attempting the JOIN query is

    "JOIN expression not supported"

  10. #10
    Join Date
    Jun 2010
    Posts
    9

    Need help with a query for my fiance

    I originally posted this in the ANSI SQL forum but after a suggestion from another member I'm posting here as well in the hopes an Access guru can help.

    Here's the post: http://www.dbforums.com/ansi-sql/165...ml#post6464678

    I can provide the Access DB if that will help.

    Thanks in advance!
    No longer relevant, both threads merged to this one. -AccessMonkey
    Last edited by Teddy; 06-14-10 at 13:09. Reason: Cleanup

  11. #11
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by Deeto View Post
    That query returns the same mixed resultset. Strange.
    So ... it worked?

  12. #12
    Join Date
    Jun 2010
    Posts
    9
    Yeah, the NOT EXISTS query was the one that was working, just not returning the right results. The EXCEPT ALL query is the one that didn't work along with the JOIN query that threw an error saying JOINS weren't supported.

    Per your recommendation, I posted over in the Access forum as well to see if anyone there could help. If it would help you to have a copy ot the DB, let me kow I'd be happy to send it.

    Btw just so you can see it in action, here's the webpage:

    http://www.dnf-racing.com/RB2.asp

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Could you share with us the actual query you ran for the exists clause that did not return the correct results?
    Dave

  14. #14
    Join Date
    Jun 2010
    Posts
    9
    Sure!

    Code:
    SELECT *
    FROM tblSongs AS hard
    WHERE 
        hard.Difficulty = 'Hard'
        AND hard.Performance > 96
        AND NOT EXISTS (
            SELECT 1 
            FROM tblSongs AS expert
            WHERE 
                hard.SongName = expert.SongName
                AND expert.Difficulty = 'Expert'

  15. #15
    Join Date
    May 2008
    Posts
    277
    The query looks right -- although you didn't close the parantheses, but I'm guessing that's a copying mistake. I don't know if Access behaves any different with single or double quotes.

    How about a little sample data to show what's happening: some input, expected output, actual output?

Posting Permissions

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