枚举列上的SQL联接返回所有可能的值

Question

我有一个选择枚举列并执行联接的SQL查询。以下是涉及到的DB表和相关数据：

优惠表格

-- 
-- Table structure for table `offer`
-- 

CREATE TABLE `offer` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `categoryID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `details` text NOT NULL,
  `amount` decimal(11,0) NOT NULL,
  `maxAmount` decimal(11,0) NOT NULL default '0',
  `dateExpires` int(11) NOT NULL default '0',
  `active` enum('YES','NO') NOT NULL,
  `featured` enum('YES','NO') NOT NULL default 'NO',
  `adType` enum('OFFER','URL') NOT NULL default 'OFFER',
  `URL` varchar(255) NOT NULL,
  `address1` varchar(50) NOT NULL,
  `address2` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`),
  KEY `categoryID` (`categoryID`),
  KEY `zip` (`zip`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;

-- 
-- Dumping data for table `offer`
-- 

INSERT INTO `offer` (`id`, `companyID`, `categoryID`, `dateAdded`, `details`, `amount`, `maxAmount`, `dateExpires`, `active`, `featured`, `adType`, `URL`, `address1`, `address2`, `city`, `state`, `zip`) VALUES 
(24, 10, 6, '2011-05-19 14:55:54', 'Computer Software & Books', 25, 0, 0, 'NO', 'NO', 'OFFER', '', '912 N. Avenue 57', '', 'Los Angeles', 'CA', '90043')

支付表

-- 
-- Table structure for table `payments`
-- 

CREATE TABLE `payments` (
  `id` int(11) NOT NULL auto_increment,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `offerID` int(11) NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  `memberID` int(11) NOT NULL,
  `validationNumber` varchar(20) NOT NULL,
  `status` enum('NEW','USED') NOT NULL default 'NEW',
  `dateRedeemed` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `validationNumber` (`validationNumber`),
  KEY `offerID` (`offerID`),
  KEY `memberID` (`memberID`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

-- 
-- Dumping data for table `payments`
-- 

INSERT INTO `payments` (`id`, `date`, `offerID`, `amount`, `memberID`, `validationNumber`, `status`, `dateRedeemed`) VALUES
(1, '2011-03-28 16:33:24', 24, 11.00, 8, '1A2B3-1', 'NEW', '2011-07-18 15:00:41'),
(2, '2011-04-12 12:47:58', 16, 81.10, 8, '2C3D4-2', 'NEW', '0000-00-00 00:00:00'),
(3, '2011-05-19 19:50:58', 24, 22.15, 14, 'ABCDE-3', 'USED', '2011-07-18 15:03:00'),
(4, '2011-05-19 19:50:58', 24, 44.30, 5, 'FGHIJK-4', 'USED', '2011-07-18 15:03:45');

事务处理表

-- 
-- Table structure for table `transactions`
-- 

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) NOT NULL,
  `offerID` int(11) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('CREDIT','DEBIT') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `companyID` (`companyID`,`offerID`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ;

-- 
-- Dumping data for table `transactions`
-- 

INSERT INTO `transactions` (`id`, `companyID`, `offerID`, `date`, `amount`, `type`) VALUES
(1, 10, 24, '2011-05-20 11:25:35', 11.00, 'CREDIT'),
(2, 10, 24, '2011-05-20 11:25:35', 22.15, 'CREDIT'),
(3, 10, 24, '2011-05-20 11:26:11', 44.30, 'CREDIT'),
(4, 10, 0, '2011-05-20 14:55:13', -3.50, 'DEBIT'),
(5, 10, 0, '2011-05-20 14:59:50', -5.00, 'DEBIT'),
(6, 10, 0, '2011-05-20 15:45:29', -4.50, 'DEBIT'),
(36, 10, 0, '2011-07-08 15:03:06', -10.00, 'DEBIT'),
(37, 10, 0, '2011-07-08 15:03:19', -2.45, 'DEBIT');

下面是查询：

SELECT DISTINCT t.*, o.amount AS discount, p.status FROM transactions t 
            LEFT JOIN offer o ON o.id = t.offerID 
            LEFT JOIN payments p ON p.offerId = t.offerID
            WHERE t.companyID = '10' ORDER BY date ASC

最后，下面是结果集：

id  companyID   offerID     date    amount  type    discount    status
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  2011-05-20 11:26:11     44.30   CREDIT  25  NEW
4   10  0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

问题是信用结果出现了两次，无论是新的枚举值还是使用的枚举值。我只想要payments行设置为的枚举值。如果有人能帮助我理解为什么这个查询不能工作，以及如何修复它，我将不胜感激。提前感谢！

@michael，@chopikadze：

我已经将memberID添加到transactions表中。我对我的查询做了如下修改：

SELECT t. * , o.amount AS discount, p.status
FROM transactions t
LEFT JOIN offer o ON o.id = t.offerID
LEFT JOIN payments p ON p.offerId = t.offerID
LEFT JOIN members m ON m.id = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

这是我的新结果集：

id  companyID   offerID     memberID    date    amount  type    discount    status
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  NEW
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
1   10  24  8   2011-05-20 11:25:35     11.00   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  NEW
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
2   10  24  14  2011-05-20 11:25:35     22.15   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  NEW
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
3   10  24  5   2011-05-20 11:26:11     44.30   CREDIT  25  USED
4   10  0   0   2011-05-20 14:55:13     -3.50   DEBIT   NULL    NULL
5   10  0   0   2011-05-20 14:59:50     -5.00   DEBIT   NULL    NULL
6   10  0   0   2011-05-20 15:45:29     -4.50   DEBIT   NULL    NULL
36  10  0   0   2011-07-08 15:03:06     -10.00  DEBIT   NULL    NULL
37  10  0   0   2011-07-08 15:03:19     -2.45   DEBIT   NULL    NULL

添加memberID似乎导致了更多重复的结果。我做错什么了？请在这一点上纠正我，伙计们。谢谢。

Answer 1

SELECT t. * , o.amount AS discount, p.status
FROM transactions t
    LEFT JOIN offer o ON o.id = t.offerID
    LEFT JOIN payments p ON p.offerId = t.offerID and p.memberID = t.memberID
WHERE t.companyID = '10'
ORDER BY date ASC

就像我说的，你不仅应该

将memberID字段添加到事务表中

但同时也

不仅在OfferID上，而且在memberID too

上加入交易和支付