JOIN problem

[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.