Mysql从多个表中连接列

I have 1 main table and two tables that hold multiple dinamyc information about the first table.

我有1个主表和两个表,其中包含有关第一个表的多个dinamyc信息。

The first table called 'items' holds main information. Then there are two tables (ratings and indexes) that holds information about some values for dinamyc count of auditories and time period.

第一个名为“items”的表包含主要信息。然后有两个表(评级和索引),其中包含有关视听的dinamyc计数和时间段的某些值的信息。

What i want: When I query for those items, I want result to have an additional column names from ratings and indexes tables.

我想要的:当我查询这些项目时,我希望结果从评级和索引表中获得额外的列名。

I have the code like this

我有这样的代码

SELECT items.*, ratings.val AS rating, indexes.val AS idx
FROM items,ratings,indexes 
WHERE items.date>=1349902800000 AND items.date <=1349989199000 
AND ratings.period_start <= items.date 
AND ratings.period_end > items.date 
AND ratings.auditory = 'kids'
AND indexes.period_start <= items.date 
AND indexes.period_end > items.date 
AND indexes.auditory = 'kids'
ORDER BY indexes.added, ratings.added DESC

The tables look something like this

表格看起来像这样

items:

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`date` bigint(40) DEFAULT NULL
PRIMARY KEY (`id`)

ratings:

`id` bigint(50) NOT NULL AUTO_INCREMENT,
`period_start` bigint(50) DEFAULT NULL,
`period_end` bigint(50) DEFAULT NULL,
`val` float DEFAULT NULL,
`auditory` varchar(200) DEFAULT NULL,
`added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)

All dates except 'added' fields which are simple TIMESTAMPS are BIGINT format - miliseconds from whatever date it is in AS3 when you do Date.getTime();

除了'添加'字段之外的所有日期都是简单的TIMESTAMPS是BIGINT格式 - 当您执行Date.getTime()时,从AS3中的任何日期开始的毫秒数;

So - what is the correct way to get this acomplished?

那么 - 获得此项目的正确方法是什么?

1 个解决方案

#1

The only thing I'm not seeing is the unique correlation of any individual ITEM to its ratings... I would think the ratings table would need an "ItemID" to link back to items. As it stands now, if you have 100 items within a given time period say 3 months... and just add all the ratings / reviews, but don't associate those ratings to the actual Item, you are stuck. Put the ItemID in and add that to your WHERE condition and you should be good to go.

我唯一没有看到的是任何单个ITEM与其评级的独特关联......我认为评级表需要一个“ItemID”来链接回项目。现在看来,如果你在给定的时间段内有100个项目,比如3个月...并且只是添加所有评级/评论,但不将这些评级与实际项目相关联,那么你就会陷入困境。将ItemID放入并将其添加到WHERE条件中,您应该好好去。