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 wins
Reply: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.