MySQL Problem writing query

I currently have a table which holds catalog information with the following fields:

catalog, pr开发者_如何学Pythonice, product_id, size, pack_size, stock_level

A product can appear in the catalog up to 30 times (the same product with a different size and/or in a different pack size must have a unique catalog_id)

I now want to display a table which has sizes down the left, pack sizes across the top and prices as the data, but don't know how to write the query.

SELECT 
  ci.size
  ,sum(if(ci.pack_size=1,ci.price,null)) as pack_size1
  ,sum(if(ci.pack_size=2,ci.price,null)) as pack_size2
  ,sum(if(ci.pack_size=3,ci.price,null)) as pack_size3
  ,sum(if(ci.pack_size=4,ci.price,null)) as pack_size4
FROM catalog_info ci
WHERE ci.product_id = 345
GROUP BY ci.size -- WITH ROLLUP

If you uncomment the "WITH ROLLUP" MySQL will add an extra grant total row at the end.

Same query for multiple products.

SELECT 
  ci.product_id
  ,ci.size
  ,sum(if(ci.pack_size=1,ci.price,null)) as pack_size1
  ,sum(if(ci.pack_size=2,ci.price,null)) as pack_size2
  ,sum(if(ci.pack_size=3,ci.price,null)) as pack_size3
  ,sum(if(ci.pack_size=4,ci.price,null)) as pack_size4
FROM catalog_info ci
WHERE ci.product_id BETWEEN 1 AND 100
GROUP BY ci.product_id ASC, ci.size DESC -- WITH ROLLUP

Note that with rollup you cannot add an order by clause, but you can effect the implicit ordering in the group clause (ASC, DESC) as shown above. Uncomment the with rollup to add a grant total row.

So, you're looking to get a group of all red widgets, including:

• Small red widget
• Small red widget (pack of 5)
• Medium red widget
• Medium red widget (pack of 5)
• Large red widget
• Large red widget (pack of 5)

They all have separate IDs. If you can guarantee that the IDs will always be in a consecutive range, you could do something like

SELECT * FROM products WHERE product_id BETWEEN 45 AND 50

However, maintaining the consistency of the ID numbers would be a pain in the butt. The next approach would be to just add a whole bunch of OR clauses:

SELECT * FROM products WHERE 
   product_id = 45
   OR product_id = 46
   OR product_id = 47
   OR product_id = 87
   OR product_id = 93

But that again wouldn't solve the problem that you don't know the numbers of all the products in a group.

So you're going to need to alter your database in one of two ways.

Method 1: alter the products table to add a new column that contains an ID number for the family of product. Thus:

ALTER TABLE products ADD COLUMN `family_id` INT(10) NOT NULL DEFAULT 0 AFTER product_id

Then manually go through and add the same family_id to groups of products:

UPDATE products SET family_id = 1 WHERE product_id = 45;
UPDATE products SET family_id = 1 WHERE product_id = 46;
UPDATE products SET family_id = 1 WHERE product_id = 47;
UPDATE products SET family_id = 1 WHERE product_id = 48;

So that you can then get them all out with a simple query like this:

SELECT * FROM products WHERE family_id = 1;

Method 2: is to create a second table to track relationships between individual products and product families:

CREATE TABLE `families` (
`relationship_id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`family_id` INT(10) NOT NULL DEFAULT 0,
`product_id` INT(10) NOT NULL DEFAULT 0
);

Then put all your relationship data in there:

INSERT INTO families (family_id, product_id) VALUES (1, 45);
INSERT INTO families (family_id, product_id) VALUES (1, 46);
INSERT INTO families (family_id, product_id) VALUES (1, 47);
INSERT INTO families (family_id, product_id) VALUES (1, 87);

And you can get it out again with a join:

SELECT p.* FROM products p, families f 
   WHERE p.product_id = f.product_id 
   AND family_id = 1;

I would tend to prefer the second solution personally, but either way will work.