使用Count（）和Group By子句进行表连接的问题... [英] Problem with table join using both Count() and Group By clause...

问题描述

在我将新的昂贵的笔记本电脑扔出窗外之前！


我坚持用列数中的正确数量来确保连接正确。几个星期前与某人交谈后，他们建议重新设计我的数据库以使用更多的信息表。在我有一张表格来获取图像信息之前，我现在已经有9张了，而且它变得非常非常混乱...特别是因为我对连接不太了解...！


我正在使用ASP，MySQL，IIS和VBScript。


我的9个表格包括：

< b>图像

ImageID，高度，宽度，DateCreated，方向

关键字

KeywordID

关键字

ImageKeyword

ImageId

KeywordID

人

PeopleID

人们

ImagePeople

ImageID

PeopleID

活动

活动ID

活动名称

ImageEvent

ImageID

EventID

摄影师

PhotographerID

FirstName

姓氏

Byline

ImagePhotographer

ImageID

摄影师ID


我的代码如下：

展开 | 选择 | Wrap | 行号

解决方案

< blockquote class =post_quotes>
在我把新的昂贵的笔记本电脑扔出窗外之前！


我坚持用列数中的正确数量来确保连接正确。几个星期前与某人交谈后，他们建议重新设计我的数据库以使用更多的信息表。在我有一张表格来获取图像信息之前，我现在已经有9张了，而且它变得非常非常混乱...特别是因为我对连接不太了解...！

规范化数据库对于初学者来说是一项艰巨的任务，所以我绝对可以同情你，但是当你掌握它时，它更有意义。把它挂在那里！

我正在使用ASP，MySQL，IIS和VBScript。


我的9个表包括：

图像

ImageID，高度，宽度，DateCreated，方向

关键字

KeywordID

关键字

ImageKeyword

ImageId

KeywordID

人

PeopleID

人们

ImagePeople

ImageID

PeopleID

活动

EventID

EventName

ImageEvent

ImageID

EventID

摄影师

摄影师ID

FirstName

姓氏

Byline

ImagePhotographer

ImageID

PhotographerID

我不认为自己是专家，但是
$ b对我来说更有意义$ b除以这样：

图片

ImageID

高度

宽度

DateCreated

定位

PhotographerID（每张照片仅由一位摄影师拍摄，因此您不需要图像摄影师表）

EventID（每张照片只在一个活动中拍摄，因此您不需要imageEvent表格）

关键字

KeywordID

关键字

ImageKeyword

ImageId

KeywordID

人

PeopleID

人们

ImagePeople

ImageID

PeopleID

活动

EventID

EventName

摄影师

摄影师ID

FirstName

姓氏

Byline

希望如果它对你有用，它应该显示一个事件列表匹配存储在会话变量中的搜索条件。


例如，如果维多利亚·贝克汉姆参加2008年奥斯卡颁奖典礼和2007年MTV大奖，并且我搜索了她的名字，那就会显示......

2008年2月18日

奥斯卡奖2008（105图片）

洛杉矶，美国。


07年7月7日

MTV Awards 2008（65图片）

柏林，德国。


看起来很简单，但我很困惑，并且项目开始受到伤害......我知道我的GROUP BY子句是错误的因为COUNT返回的金额，也可能将错误的列连接在一起。


我有机会获得SQL字符串的帮助......？如果我看到与我的垃圾版本相比的正确版本，我想我会很快学到它。比我已经解决这个问题的26个小时更快......！


非常感谢，从一个有压力的业余爱好者...

这种输出格式很难用SQL做。这可能是可行的，但是我必须这样做;我必须考虑它并查阅文本。问题是要么是GROUP BY，要么是GROUP BY。语句或SELECT DISTINCT语句将多个记录压缩成单个记录，这就是count（）关闭的原因。当我在过去试图做这种事情的时候，我已经提出了所有的记录并计入了剧本。我现在正试图刷新我的SQL，所以我会再次研究它。


如果这有帮助，请告诉我。


Jared

您好，

事件表需要更多详细信息，例如EventDate和Location。一旦你添加了那些，你应该能够得到以下内容：

展开 | 选择 | Wrap | 行号

感谢你的时间......


我通过运行这个脚本实际修复了我的主要问题（第一篇文章）：

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

Before I throw my new expensive laptop out of the window!

I''m stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they suggested I redesigned my database to use more tables of info. Before I had 1 table for image information, now I have 9 and it''s getting very, very confusing... especially as I don''t know much about joins...!

I am using ASP, MySQL, IIS and VBScript.

My 9 tables consist of:

Images
ImageID, Height, Width, DateCreated, Orientation

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

ImageEvent
ImageID
EventID

Photographers
PhotographerID
FirstName
LastName
Byline

ImagePhotographer
ImageID
PhotographerID


My code is as follows:

Expand|Select|Wrap|Line Numbers

解决方案

Before I throw my new expensive laptop out of the window!

I''m stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they suggested I redesigned my database to use more tables of info. Before I had 1 table for image information, now I have 9 and it''s getting very, very confusing... especially as I don''t know much about joins...!

Normalizing a db is a daunting task for a beginner so I can definitely commiserate with you, but as you get the hang of it it makes more sense. hang it there!

I am using ASP, MySQL, IIS and VBScript.

My 9 tables consist of:

Images
ImageID, Height, Width, DateCreated, Orientation

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

ImageEvent
ImageID
EventID

Photographers
PhotographerID
FirstName
LastName
Byline

ImagePhotographer
ImageID
PhotographerID

I wouldn''t really consider myself an expert, but it makes more sense to me to
divide it up like this:

Images
ImageID
Height
Width
DateCreated
Orientation
PhotographerID (each photo is only taken by one photographer, so you don''t need a imagePhotographer table)
EventID (each photo was taken at only one event, so you don''t need a imageEvent table)

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

Photographers
PhotographerID
FirstName
LastName
Byline

Hopefully, if it makes sense to you, it should display a list of events that matches the search criteria stored in the session variables.

As an example, If Victoria Beckham was at the Oscar Awards 2008 and MTV Awards 2007 and I searched for her name, it would show...

18 FEB 08
Oscar Awards 2008 (105 Images)
Los Angeles, USA.

07 JUL 07
MTV Awards 2008 (65 Images)
Berlin, Germany.

It seems pretty simple but I am so confused and behind with the project its starting to hurt a bit.... I know my GROUP BY clause is wrong because of the amount the COUNT is returning, and also maybe joining the wrong columns together.

Any chance I can get some help with the SQL string...? If I see the correct version compared to my crap version, I think I will learn from it quickly. Quicker than the 26 hours I''ve put into this problem already...!

Many thanks in advance, from a stressed amateur...

This output format is hard to do with just SQL. It might be doable, but I woul;d have to think about it and consult a text. The problem is that either the "GROUP BY" statement or the SELECT DISTINCT" statement condense multiple records into a single record, this is why the count() is off. When I have tried to do this type of thing in the past I have brought up all the records and counted in the script. I am currently trying to brush up on my SQL though, so I will look into it again.

Let me know if this helps.

Jared

Hi,
The Event Table will need further details such as EventDate and Location. Once you''ve added those you should be able to something like the following:

Expand|Select|Wrap|Line Numbers

Thanks for your time...

I have actually fixed my main problem (first post) by running this script:

Expand|Select|Wrap|Line Numbers

这篇关于使用Count（）和Group By子句进行表连接的问题...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！