复杂的内连接？ [英] Complicated inner join??

问题描述

好的帮派。这里有一些复杂的东西，好吧，至少对我而言。

使用Access DB


我的数据库中有一个名为members的表。在该表中，我有2个表格我将使用用户名和用户名。和点


现在，我还有一个名为all_matches的表。此表包含每场比赛

报告。超过25,000人。我有一个用户名字段和结果字段字段a

" username1"字段和extra_match字段。


我需要做的是做一个从表中获取用户名的查询

" members"并使用用户名找到：

在会员表中的字段，我需要获得统计数据

给我以下内容。


1.）总共有多少场比赛。这只是all_matches表中

用户名字段的计数。由于人们报告比赛胜利

或输了。

2.）多少胜利，其中extra_match = 0.这将是一个用户名
$ b all_matches表中的$ b，其中结果是=''赢''和extra_match = 0.

3.）多少次胜利，其中extra_match = 1.这将是all_matches表中的用户名

的计数其中结果 =''赢''和extra_match = 1

4.）多少损失，其中extra_match = 0.这将是all_matches表中

用户名的计数"结果" ='''失去''和

extra_match = 0 ..

5.）多少损失，其中extra_match = 1.这将是
all_matches表中的
用户名，其中结果为=''失去'和

extra_match = 1.

6.）然后将胜利分成总比赛并获得胜利％

现在我做了这个，使用前50名因此它不是太强烈，因为在all_matches表中有很多行。但我不能做的就是获得正确的分类

。但我认为如果我可以在访问中执行此操作，而不是在asp

页面上执行此操作，则会更容易。


我有一个是在另一个数据库上为我完成，但我无法修改它

这个数据库。

我可以向你展示我在asp页面上所做的脚本，但是相反，它很邋b，而且无论如何都不行。所以我需要帮助在

访问中创建这个SQL作为查询。


我知道这很多，

但是任何帮助将不胜感激。我知道基本的SQL，但我不知道内部

和外连接。

谢谢

bam

解决方案

Jeff写道：

Ok gang。这里有一些复杂的东西，至少对我而言。
使用Access DB

我的数据库中有一个名为members的表。在该表格中，我将使用2个表格用户名和点


请在表格描述中包含数据类型


表：成员

列：用户名文字

分数？长？单身？

现在，我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match领域。


同样的交易。 username1的目的是什么？

我需要做的是使用表格中的用户名进行查询
" members"并找到：
使用用户名在成员表中的字段中，我需要获取给我以下内容的统计数据。

1.）总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。


SELECT mb.username，count（m.username）as TotalMatches

来自会员的mb左连接匹配为m

ON mb.username = m.username

GROUP BY mb.username


2.）多少次胜利，其中extra_match = 0.这将是一个计数在all_matches表中
用户名，其中结果是=''赢''和
extra_match = 0。


< FROM和GROUP子句不会改变，所以我不会重复然后>


SELECT mb.username，count（m .username）作为TotalMatches，

SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins


3.）有多少胜利，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果为=''赢''和
extra_match = 1


SELECT mb.username，count（m.username）为TotalMatches，

SUM（ Iif（[结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，

SUM（Iif（[结果] =''赢''和[extra_match ] = 1,1,0））作为TotalExtraWins

4.）多少损失，其中extra_match = 0.这将是all_matches表中
用户名的计数，其中结果 ; =''丢失''和
extra_match = 0 ..


SELECT mb.username，count（m.username）为TotalMatches，

SUM（Iif（[outcome] =''Win''和[extra_match] = 0,1,0））作为TotalWins，

SUM（Iif（[results] =''Win''AND [extra_match] = 1,1,0））作为TotalExtraWins，

SUM（Iif（[结果] =''损失''和[extra_match] = 0,1,0））作为TotalLosses <
5.）多少损失，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果是=''失去''和
extra_match = 1。


SELECT mb.username，count（m.username）为TotalMatches，

SUM（Iif（[outcome] =''Win''和[extra_match ] = 0,1,0））作为TotalWins，

SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0））作为TotalExtraWins，

SUM（Iif（[结果] =''损失''和[extra_match] = 0,1,0））作为TotalLosses，

SUM（Iif（[结果] = ''亏损'和[extra_match] = 1,1,0））作为TotalExtraLosses


6.）然后将胜利分成总游戏并获得胜利％


是胜利定义为TotalWins + TotalExtraWins？如果是这样的话：

SELECT mb.username，count（m.username）为TotalMatches，

SUM（Iif（[outcome] =''Win''和[extra_match] = 0,1,0））作为TotalWins，

SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0））作为TotalExtraWins，
SUM（Iif（[结果] =''亏损'和[extra_match] = 0,1,0））作为TotalLosses，

SUM（Iif（[results] =' 'Loss''和[extra_match] = 1,1,0））作为TotalExtraLosses，

（SUM（Iif（[results] =''Win''和[extra_match] = 0,1， 0））+

SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0）））/

count（m .username）为WinPercent

< snip>我知道这很多，
但是任何帮助都会受到赞赏。我知道基本的SQL，但我不知道
内部和外部联接。

:-)

那是IS 基本SQL ...

这是GROUP BY和使用Iif将其带入高级

领域。 :-)


Bob Barrows


-

Microsoft MVP - ASP / ASP.NET

请回复新闻组。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

" Bob Barrows [MVP] " <再****** @ NOyahoo.SPAMcom>在消息中写道

新闻：％2 **************** @ TK2MSFTNGP10.phx.gbl ...

Jeff写道：

Ok gang。这里有一些复杂的东西，至少对我而言。
使用Access DB

我的数据库中有一个名为members的表。在该表格中，我将使用2个表格用户名和积分
请在表格描述中包含数据类型

表：成员
栏目：用户名文字
分数？长？单身？

ok。 username是55 ch文本，points是长整数。

现在，我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match领域。
同样的交易。 username1的目的是什么？

用户名1的目的是报告的人是用户名...

他报告说他演奏的是username1。我接管了这个，并且

无法将所有内容排序，并使其更容易阅读。

什么我需要做的是，使用表格中的用户名进行查询
" members"并找到：
使用用户名在成员表中的字段中，我需要获取给我以下内容的统计数据。

1.）总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。
SELECT mb.username，count（m.username）为TotalMatches
FROM成员为mb左连接匹配为m
ON mb。 username = m.username
GROUP BY mb.username

2.）多少次胜利，其中extra_match = 0.这将是 extra_match = 0.

< FROM和GROUP子句不会改变，所以我不会重复然后>

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[outcome] =''Win''和[extra_match] = 0,1,0））为TotalWins

3.）多少次胜利，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果为=''赢''和
extra_match = 1

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[结果] ] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0） ）作为TotalExtraWins

4.）多少损失，其中extra_match = 0.这将是all_matches表中
用户名的计数，其中结果为=''丢失''和
extra_match = 0 ..

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（ [结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1， 0））作为TotalExtraWins，
SUM（Iif（[results] =''Loss''和[extra_match] = 0,1,0））作为TotalLosses

5.）多少损失，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果是=''丢失''和
extra_match = 1.

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0 ））作为TotalExtraWins，
SUM（Iif（[结果] =''亏损'和[extra_match] = 0,1,0））作为TotalLosses，
SUM（Iif（[结果] =' 'Loss''和[extra_match] = 1,1,0））作为TotalExtraLosses

6.）然后将胜利分成总游戏并获得胜利％

是胜利定义为TotalWins + TotalExtraWins？如果是这样的话：

是的，TotalWins将是extra_match = 0和extra_match = 1的总和。

SELECT mb.username，count（ m.username）作为TotalMatches，
SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0））作为TotalExtraWins，
SUM（Iif（[结果] =''丢失''和[extra_match] = 0,1,0））作为TotalLosses，
SUM（Iif（[结果] =''亏损'和[extra_match] = 1,1,0））作为TotalExtraLosses，
（SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））+
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0）））/
count（m.username）为WinPercent
< snip>

我知道这很多，
但任何帮助都会受到赞赏。我知道基本的SQL，但我不知道
内部和外部联接。

:-)
这是基本SQL。 ......这是GROUP BY和使用Iif将其带入高级领域。 :-)

Bob Barrows

-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将得到更快的回复。

好​​的，所以我需要将所有这些放入sql访问。希望没有什么

随着我提出的问题后的信息而改变。实际上，

用户名1甚至不应该为此发挥作用我不会想到。

感谢Bob

Jeff

现在如果我想按TotalWins排序，我会在

GROUP BY之前添加它吗？

Bob Barrows [MVP]" <再****** @ NOyahoo.SPAMcom>在消息中写道

新闻：％2 **************** @ TK2MSFTNGP10.phx.gbl ...

Jeff写道：

Ok gang。这里有一些复杂的东西，至少对我而言。
使用Access DB

我的数据库中有一个名为members的表。在该表格中，我将使用2个表格用户名和点

请在表的描述中包含数据类型

表：成员
列：用户名文本
点数？长？ Single？

现在，我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match field。

相同的交易。 username1的目的是什么？

我需要做的是，使用表格中的用户名进行查询
" members"并找到：
使用用户名在成员表中的字段中，我需要获取给我以下内容的统计数据。

1.）总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。

SELECT mb.username，count（m.username）为TotalMatches
FROM成员为mb left join匹配为m
ON mb.username = m.username
GROUP BY mb.username

2.）多少次胜利，其中extra_match = 0。这将是all_matches表中
用户名的计数，其中结果是=''赢''和
extra_match = 0.

< FROM和GROUP子句不会改变，所以我不会重复然后>

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[outcome] =''Win''和[extra_match] = 0,1,0））为TotalWins

3.）多少次胜利，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果为=''赢''和
extra_match = 1

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[结果] ] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0） ）作为TotalExtraWins

4.）多少损失，其中extra_match = 0.这将是all_matches表中
用户名的计数，其中结果为=''丢失''和
extra_match = 0 ..

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（ [结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1， 0））作为TotalExtraWins，
SUM（Iif（[results] =''Loss''和[extra_match] = 0,1,0））作为TotalLosses

5.）多少损失，其中extra_match = 1.这将是all_matches表中
用户名的计数，其中结果是=''丢失''和
extra_match = 1.

SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0 ））作为TotalExtraWins，
SUM（Iif（[结果] =''亏损'和[extra_match] = 0,1,0））作为TotalLosses，
SUM（Iif（[结果] =' 'Loss''和[extra_match] = 1,1,0））作为TotalExtraLosses

6.）然后将胜利分成总游戏并获得胜利％

是胜利定义为TotalWins + TotalExtraWins？如果是这样的话：
SELECT mb.username，count（m.username）为TotalMatches，
SUM（Iif（[result] =''Win''和[extra_match] = 0,1,0））作为TotalWins，
SUM（Iif（[结果] =''赢''和[extra_match] = 1,1,0））作为TotalExtraWins，
SUM（Iif（[结果] =''损失''AND [extra_match] = 0,1,0））作为TotalLosses，
SUM（Iif（[outcome] =''Loss''和[extra_match] = 1,1,0））作为TotalExtraLosses，< br（>（SUM（Iif（[结果] =''赢''和[extra_match] = 0,1,0））+
SUM（Iif（[结果] =''赢''和[extra_match ] = 1,1,0）））/
计数（m.username）为WinPercent
< snip>

我知道这很多，
但任何帮助将不胜感激。我知道基本的SQL，但我不知道
内部和外部联接。

:-)
这是基本SQL。 ......这是GROUP BY和使用Iif将其带入高级领域。 :-)

Bob Barrows

-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将得到更快的回复。

Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB

I have a table in my DB called members. In that table, I have 2 tables I
will be using "username" and "points"

Now, I also have a table called all_matches. This table contains every match
report. Over 25,000 of them. I have a "username" field an "outcome" field an
"username1" field and "extra_match" field.

What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain stats
that give me the following.

1.) how many total matches played. Which would simply be a count of the
username field in the all_matches table. Since people report the match win
or lose.
2.) how many wins, where extra_match = 0. This would be a count of username
in the all_matches table where "outcome" = ''Win'' and extra_match=0.
3.) how many wins, where extra_match = 1. This would be a count of username
in the all_matches table where "outcome" = ''Win'' and extra_match=1
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
6.) Then divide the wins into the total games and get a win %

now I did this, using top 50 so it wasn''t too intensive, since there are so
many lines in the all_matches table. But what I can''t do is get the sort
right. But I think if i can do this in access, instead of doing it on an asp
page, it will be easier.

I had one that was done for me on another DB, but I was unable to modify it
for this DB.
I can show you the script of what I did on the asp page, but it is rather
sloppy, and it doesn''t work anyway. So I need help creating this SQL within
access as a query.

I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know inner
and outer joins.
THanks
bam

解决方案

Jeff wrote:

Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB

I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"
Please include datatypes in your descriptions of your tables

Table: Members
Columns: username Text
points Number? Long? Single?

Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.
Same deal. What is the purpose of "username1"?

What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.

1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.
SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username

2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.
<the FROM and GROUP clauses will not change, so I will not repeat then>

SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins

3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1
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
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
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

5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
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

6.) Then divide the wins into the total games and get a win %
Is "wins" defined as TotalWins + TotalExtraWins? if so:
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
<snip> I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.

:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Jeff wrote:

Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB

I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"
Please include datatypes in your descriptions of your tables

Table: Members
Columns: username Text
points Number? Long? Single?

ok. username is 55 ch text, points is long integer.

Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.
Same deal. What is the purpose of "username1"?

the purpose of username1 is that the person reporting is the username... the
person he is reporting that he played is username1. I took over this, and
haven''t been able to sort everything out, and make it easier to read yet.

What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.

1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.
SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username

2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.

<the FROM and GROUP clauses will not change, so I will not repeat then>

SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins

3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1

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

4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..

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

5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.

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

6.) Then divide the wins into the total games and get a win %

Is "wins" defined as TotalWins + TotalExtraWins? if so:

yes, TotalWins would be the total from extra_match = 0 and extra_match = 1.
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
<snip>

I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.

:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

Ok, so I need to put all these into the sql in access. Hopefully nothing
changed with the info I gave after your questions. In all actuality, the
username1 shouldn''t even come into play for this I don''t think.
Thanks Bob
Jeff

Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY??
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Jeff wrote:

Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB

I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"

Please include datatypes in your descriptions of your tables

Table: Members
Columns: username Text
points Number? Long? Single?

Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.

Same deal. What is the purpose of "username1"?

What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.

1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.

SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username

2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.

<the FROM and GROUP clauses will not change, so I will not repeat then>

SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins

3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1

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

4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..

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

5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.

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

6.) Then divide the wins into the total games and get a win %

Is "wins" defined as TotalWins + TotalExtraWins? if so:
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
<snip>

I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.

:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

这篇关于复杂的内连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！