[eluser]wowdezign[/eluser]
I am not sure I am following then. Am I totally lost or is something like this what he's looking for?
Sample Orders Table:
Code:
CREATE TABLE IF NOT EXISTS `orders` (
`order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) NOT NULL,
`order_date` date NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `orders` (`order_id`, `customer_id`, `order_date`) VALUES
(1, 2, '2010-01-19'),
(2, 1, '2010-01-26'),
(3, 3, '2010-01-22'),
(4, 13, '2010-01-22'),
(5, 2, '2010-01-28');
Sample Orders Items Table:
Code:
CREATE TABLE IF NOT EXISTS `orders_items` (
`product_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `orders_items` (`product_id`, `order_id`) VALUES
(1, 1),
(2, 1),
(4, 1),
(3, 1),
(1, 2),
(4, 2),
(35, 5),
(136, 5);
Then when I run this:
Code:
SELECT *
FROM (`orders_items`)
LEFT JOIN `orders` ON `orders_items`.`order_id` = `orders`.`order_id`
WHERE `orders`.`customer_id` = '2'
I get a result set of all the items bought by customer number 2 and they are grouped by the order id.
I thought that was the desired result.
This is what LEFT JOINS are great for IMO.
Sorry, if I misunderstood. I was just trying to help.