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.
