Complicated inner join??

[Bob Barrows [MVP]]

Why not? You're simply replacing a recordset loop with an array loop.

 

[Bob Barrows [MVP]]

Yep. I failed to hold the shift key down while typing the open parenthesis
and got a "9" instead. The statement should be:

for i=0 to ubound(arData,2)

not

for i=0 to ubound9arData,2)

 

[Jeff]

the one that you wrote.. i put it IN Access... and when I add that to the
end, and go to the table view.. it prompts me.

 

[Bob Barrows [MVP]]

When (in Access) I put in any kind of ORDER BY, it prompts me for a
paramater. So what I was doing was calling the query from asp page.
In the query itself, when I look at in access, it is sorted by
username. So in my query on the asp page, I am calling this:

Oh, wait, I think i know what the issue is. You tried to do this:

SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username
ORDER BY TotalWins DESC

and Access balked, right?

There are two options:

1. repeat the calculation in the ORDER BY:

....
ORDER BY SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))


2. Use a subquery:
SELECT top 50 username, TotalMatches, TotalWins,
TotalExtraWins, TotalLoses, TotalExtraLosses, WinPercent FROM
(SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username) as q
ORDER BY TotalWins DESC

As to why the sort order is not correct, are you confusing TotalWins (where
extra_match = 0) with total wins (regular wins plus extra match wins)?

Bob barrows

 

[Jeff]

Bob, what I ended up doing, and it worked for me, is adding to the
statement.
I added
(SUM(IIf([outcome]='Win' And [extra_match]=0,1,0))+SUM(IIf([outcome]='Win'
And [extra_match]=1,1,0))) AS TotalWinsAll

That way, I could sort by that, and it does come out right.

Thanks a bunch

 

[Jeff]

One more thing on this please Bob,
If I wanted to narrow it down more, and add WHERE member_status = 'Active'
this would be the table that you got the username out of to begin with.
would it go something like

FROM members as mb where mb.member_status = 'Active' left join matches as m
ON mb.username= m.username
GROUP BY mb.username

Bob, what I ended up doing, and it worked for me, is adding to the
statement.
I added
(SUM(IIf([outcome]='Win' And [extra_match]=0,1,0))+SUM(IIf([outcome]='Win'
And [extra_match]=1,1,0))) AS TotalWinsAll

That way, I could sort by that, and it does come out right.

Thanks a bunch

And I get an error there Bob

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/tour_rankings.asp, line 46

for i=0 to ubound9arData,2)

 

[Bob Barrows [MVP]]

One more thing on this please Bob,
If I wanted to narrow it down more, and add WHERE member_status =
'Active' this would be the table that you got the username out of to
begin with. would it go something like

FROM members as mb where mb.member_status = 'Active' left join
matches as m ON mb.username= m.username
GROUP BY mb.username

No. The WHERE clause always follows the FROM clause (which contains the ON
subclauses)

FROM members as mb left join matches as m
ON mb.username= m.username
where mb.member_status = 'Active'
GROUP BY mb.username

Be careful if you use the Access Query Builder Design View to add this
condition. If you simply add the member_status field to the grid and put
'Active' in the Criteria row below it, Access will put the condition in the
HAVING clause. This will be very inefficient because this will tell Jet to
aggregate all the rows FIRST, and then filter out the resulting records that
don't meet the criteria. To force the criterion to be put in the WHERE
clause where it belongs, you need to add the field to the grid, and then
change the entry in the Total row to "Where" (using the dropdown selection).

In SQL Server, you could further optimize this query by putting the
criterion in the ON clause, like this:
FROM members as mb left join matches as m
ON mb.username= m.username and mb.member_status = 'Active'
GROUP BY mb.username

But Jet will not like this syntax. You can get around this limitation by
using a subquery (or a saved query):

FROM
(select <list of fields> from members where member_status = 'Active')
as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username

The idea is to put your search conditions as early into the process as
possible to minimize the number of records that need to be dealt with by the
query engine.

Here is a post from Joe Celko that may help you understand the inner
workings (or further confuse you <grin>):
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/ec194ab2109662c8

Bob Barrows