使用不同的ID分隔数据 - mysql或phpexcel

Question

I'm trying to separate different product category in a receipt so that I can have different query for the MAJOR category and the MINOR category using MYSQL.

MAJOR has an item code of 771013 and MINOR has an item code of 771012. That being said, I also want to get the items that have been ordered in each check.

Here is my desired output.

I'm using MYSQL and PHPExcel on how to do this. Can you give me hints on how to do my query? Is this possible with the formula of vlookup with PHPExcel?

Answer 1

To get the items in receipts

SELECT receipts_body.receipt_id,items.id,items.title FROM receipts_body
LEFT JOIN items ON items.id = receipts_body.item_id
ORDER BY receipt_id

To get the receipts with MAJOR category

SELECT receipts_body.receipt_id,items.id,items.title FROM receipts
LEFT JOIN items ON items.id = receipts_body.item_id
WHERE receipt_id IN (SELECT receipt_id FROM receipt_body WHERE item_id = 771013)
ORDER BY receipt_id

To get the receipts with MINOR category

SELECT receipts_body.receipt_id,items.id,items.title FROM receipts
LEFT JOIN items ON items.id = receipts_body.item_id
WHERE receipt_id IN (SELECT receipt_id FROM receipt_body WHERE item_id = 771012)
ORDER BY receipt_id