count number of joins

[mbeals]

I have an order tracking system.  I have one table that has the details of the actual order, including the number or items being ordered.  When we begin processing an item, an entry is created in a transaction table, linking the specific item to the order.

 

IE:

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wo` int(11) NOT NULL,
  `customer` int(11) NOT NULL,
  `num_items` int(11) NOT NULL,
  `details` text NOT NULL,
  PRIMARY KEY (`id`);

CREATE TABLE IF NOT EXISTS `order_progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL,
  `item` int(11) NOT NULL,
  `shipment` varchar(20) NOT NULL,
  `scanned` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `called` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `boxed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)

 

I need a way to only pull the records from `orders` where the number of associated records in `order_progress` are less then the number in ordes.num_items.

[Cosizzle]

Hey mbeals,

 

It sounds like you know exactly like you know what you want? I dont see the problem. If you want to compare records from order_progress you could use a COUNT(*)

 

Sorry I might be misunderstand your question... Could you give an example?

[mbeals]

I actually kind of figured it out:

 

SELECT `orders`.*, progress.* 
from `orders` left join (Select `order`, COUNT(*) as attached from order_progress group by `order`)
as progress on `orders`.id = progress.`order` 
WHERE attached < num_receivers";

 

Now the issue I have is if I have a record in `orders` and nothing that references that record in order_progress, the COUNT(*) returns NULL instead of 0, which breaks the logic in the WHERE statement.