mysql - 过滤两个表中的结果

Question

我正在将一个带有送货单的发票系统移植到 PHP + MySQL，除了我要为客户开具发票外，我已经完成了所有工作。

我正在使用这些表:

表 1 是我添加每个送货单的地方

CREATE TABLE IF NOT EXISTS `delivery_note` (
  `id_delivery_note` int(11) NOT NULL AUTO_INCREMENT,
  `number_delivery_note` int(11) DEFAULT NULL,
  `id_product` int(11) NOT NULL,
  `qty_delivered` int(11) NOT NULL,
  `qty_received` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `client_code` varchar(50) DEFAULT NULL,
  `active` int(1) DEFAULT NULL,
  `id_invoice` int(11) DEFAULT NULL,
  `invoiced` int(1) DEFAULT NULL,
  PRIMARY KEY (`id_delivery_note`)
) TYPE=MyISAM  ROW_FORMAT=DYNAMIC AUTO_INCREMENT=6 ;


INSERT INTO `delivery_note` (`id_delivery_note`, `number_delivery_note`, `id_product`, `qty_delivered`, `qty_received`, `date`, `client_code`, `active`, `id_invoice`, `invoiced`) VALUES
(2, 2, 13, 1, NULL, '2012-04-25', '1', 1, NULL, 0),
(3, 3, 24, 1, NULL, '2012-04-25', '1', 1, NULL, 0),
(5, 1, 13, 2, NULL, '2012-04-24', '2', 1, NULL, 0);

表 2 是我为每个客户保存每个商品的价格(所有客户都有不同的价格)

CREATE TABLE IF NOT EXISTS `product_price` (
  `id_product_price` int(11) NOT NULL AUTO_INCREMENT,
  `client_code` int(11) DEFAULT NULL,
  `id_product` int(11) DEFAULT NULL,
  `price` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id_product_price`)
) TYPE=MyISAM  ROW_FORMAT=FIXED AUTO_INCREMENT=6 ;


INSERT INTO `product_price` (`id_product_price`, `client_code`, `id_product`, `price`) VALUES
(1, 1, 13, 1.51),
(2, 1, 24, 43.11),
(3, 1, 24, 11.00),
(4, 2, 13, 1.52),
(5, 2, 24, 43.12);

我一直在使用的查询是:

SELECT number_delivery_note, delivery_note.id_product, qty_delivered, date, product_price.price
FROM delivery_note, product_price
WHERE(delivery_note.client_code = 1) AND (delivery_note.invoiced = 0)

然后给了我这一切:

http://s14.postimage.org/rghvfmo9t/Sin_t_tulo_1.gif

当我只需要这些数据时:

http://s15.postimage.org/8hnpv9qt7/propblema.jpg

Answer 1

我认为this可能是你想要的:

SELECT id_delivery_note, number_delivery_note, date, x.id_product, x.price,
qty_delivered FROM delivery_note d INNER JOIN product_price x
ON x.id_product = d.id_product
AND d.client_code = x.client_code WHERE d.client_code = 1 and d.invoiced = 0
GROUP BY id_product