I'm porting an invoice system with delivery notes that was on msSQL and VB2008 to PHP + MySQL, I've finished everything except when I'm going to make an invoice for a customer.
I'm using these tables:
Table 1 is where I add each delivery note
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);
Table 2 is where I keep the prices for each customer, for each item (All the customers have different prices)
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);
And the query I've been using is:
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)
And gives me all this:
http://s14.postimage.org/rghvfmo9t/Sin_t_tulo_1.gif
When I need only this data:
I think this is probably what you want:
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