多表MySQL查询

Question

我有两个要同时查询的表。这两个表分别是uploads和audienceuploadassociation，所以事情是这样的，我的表中只有一个名为"ESL“的主题值，但是当我使用下面的SQL给我6条记录时，它将相同的数据重复6次。

SELECT uploads.*, audienceuploadassociation.*
FROM   uploads
  JOIN audienceuploadassociation
    ON uploads.upload_id = audienceuploadassociation.upload_id
 WHERE uploads.member_id = '1'
   AND uploads.member_school_id='1'
   AND subject = 'ESL'
   AND topic = 'Poetry'
LIMIT  20

下面是上传的表结构：

`uploads` (
  `upload_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` varchar(255) NOT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upload_name` text NOT NULL,
  `upload_title` varchar(255) NOT NULL,
  `upload_type` varchar(100) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `topic` longtext NOT NULL,
  `year` int(100) DEFAULT NULL,
  `keywords` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  `status` enum('Finished','Work in progress','Blank template') NOT NULL DEFAULT 'Finished',
  `test_name` varchar(255) DEFAULT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `member_display_name` varchar(255) NOT NULL,
  `member_school_id` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`upload_id`)
) 

而对于audienceuploadassociation关联：

`audienceuploadassociation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `upload_id` bigint(20) NOT NULL,
  `audiencename` varchar(254) NOT NULL,
  PRIMARY KEY (`id`)
) 

任何帮助都将不胜感激，提前谢谢。

Answer 1

试试这个，使用GROUP BY：

SELECT uploads.*, audienceuploadassociation.*
FROM   uploads
  JOIN audienceuploadassociation
    ON uploads.upload_id = audienceuploadassociation.upload_id
 WHERE uploads.member_id = '1'
   AND uploads.member_school_id='1'
   AND subject = 'ESL'
   AND topic = 'Poetry'
GROUP BY uploads.upload_id
LIMIT  20

Answer 2

我看不到这两个表之间有任何关系。

在uploads表定义中更改此设置

`member_school_id` int(11) NOT NULL DEFAULT 1

从member_school_id中删除引号，因为它是一个INT字段。

SELECT uploads.*, audienceuploadassociation.*
FROM   uploads
  JOIN audienceuploadassociation
    ON uploads.upload_id = audienceuploadassociation.upload_id
 WHERE uploads.member_id = '1'
   AND uploads.member_school_id=1
   AND uploads.subject = 'ESL'
   AND uploads.topic = 'Poetry'
LIMIT  20