Friday, January 8, 2010

I'm an idiot who is coding SQL in an old Access Database?

Ok, I know this isn't high on the priority of the typical nerd's ';Problems to Solve'; list, but I'm dicking around with SQL in an old version of MS Access, and I want to know something. I've got the following query:





SELECT


user_idx,


( SELECT COUNT(*) FROM picks


WHERE pick = 1 and home_score %26gt; away_score


OR pick = 0 and away_score %26gt; home_score )


AS wins


FROM users


ORDER BY wins





...and it doesn't work. Instead of taking the value of the calculated field [wins] and using it to sort the results, it pops up an input box and asks me what the value of [wins] is! WTF? Is there some arcane notation I'm missing?





P.S. Don't tell me to get PostGreSQL or MySql. I'd love to. But this is for a hobby website, and my FREE hosting service doesn't support those!I'm an idiot who is coding SQL in an old Access Database?
I am guessing that the calculcated wins column is out of scope for the order-by clause. I would try this:





select * from (


SELECT


user_idx,


( SELECT COUNT(*) FROM picks


WHERE pick = 1 and home_score %26gt; away_score


OR pick = 0 and away_score %26gt; home_score )


AS wins


FROM users


)


ORDER BY wins





Good luck!





Ok, second try. Not sure if your version SQL supports case statements, so this is not as clean as possible, but...





SELECT


user_idx, wins


FROM users


inner join


( SELECT user_idx, COUNT(*) as wins FROM picks


WHERE pick = 1 and home_score %26gt; away_score


OR pick = 0 and away_score %26gt; home_score


group by user_idx


) as pick on users.user_idx = pick.user_idx


ORDER BY wins





Third try! [this is a tough way to debug code!]





SELECT


user_idx, wins


FROM users


inner join


( SELECT user_idx, COUNT(*) as wins FROM picks


WHERE pick = 1 and home_score %26gt; away_score


OR pick = 0 and away_score %26gt; home_score


group by user_idx


) pick on users.user_idx = pick.user_idx


ORDER BY winsI'm an idiot who is coding SQL in an old Access Database?
I'm not an sql programmer as such, but the syntax seems very similar to mysql, which is based on it.





It looks to me as if there are a couple of potential problems. First, I think you need to bracket the where statement a bit, to make it less ambiguous:


WHERE (pick=1 AND home_score%26gt;away_score) OR (pick=0 AND away_score%26gt;home_score)





Second, I don't know if it's the same, but in mysql, it would be in this order:


SELECT user_idx from users, COUNT(*) from picks WHERE (pick=1 AND home_score%26gt;away_score) OR (pick=0 AND away_score%26gt;home_score) AS wins ORDER BY wins





still not sure it will do what you are expecting though.





PS. Surely some free host somewhere must provide MySQL, i mean it IS open source.
  • home facial
  • oil repair
  • natural shampoo
  • oil repair
  • No comments:

    Post a Comment