Order Matching Query

Question

I have two tables, A & B

Table A                                 Table B

order id                       order id        quantity           
-----------                    ------------    -------------
1                               1               10
2                               2               20
3                               3               10
4                               4                5
5                               5                6

table A contains the order id and table B contains order id & quantity. I am trying to run a MySQL query that shows rows with a total equal to or less than a number. For example, a query to find rows equal to or less than 15 will show the rows with order id's 4 & 5

What have you tried already? And what is this 15..the quantity or some other imaginary value! — Neels, Mar 21 '14 at 05:35
What do you mean by 'total'? If you just mean the quantity then should row 1 and 3 be displayed also along with 4 and 5? — Aakash Jain, Mar 21 '14 at 05:36
15 is an imaginary value to construct the right query. Total is the total quantity from the rows. the total quantity for all the records in table B is 41 (10+20+10+5+6) — user3400139, Mar 21 '14 at 05:41
I can join the two tables and do stuff like quantity = 5 to get the row with order id 4 — user3400139, Mar 21 '14 at 05:43
@user3400139 Your business rule has to be a bit more generic than "if the magic value is 15, return row 4 and 5", otherwise what would happen if the magic value were 16 for example? — Joachim Isaksson, Mar 21 '14 at 05:52
@user3400139 For example, the question would make sense if 4 and 5 were on one order, and 1,2,3 were on another. Then the total for an order id would be less than 15 and the rows could be returned. As it it now in your example, all rows are for separate orders, so 4 orders are under 15 total. — Joachim Isaksson, Mar 21 '14 at 05:58
if the magic value was 16, the query will still run and return order id's 3 & 5 which totals 16 — user3400139, Mar 21 '14 at 06:01
the "magic value" changes and it should not effect the structure of the query. whatever number you plug into the magic value, the query should determine rows in table B that in total is less than or equal to the magic value irrespective of what that magic value is — user3400139, Mar 21 '14 at 06:06
Why does Table A matter here? Based on your example it has no data that Table B does not have anyway. — Kirk Logan, Mar 21 '14 at 06:07
I simplified the question. Table A does contain data which I will use. what I am trying to figure out is how to get rows from table B (when joined to A) equal or less than a magic value which can be any number really — user3400139, Mar 21 '14 at 06:12
can anybody modify the following solution to fit mine http://stackoverflow.com/questions/11689080/sql-select-elements-where-sum-of-field-is-less-than-n — user3400139, Mar 21 '14 at 07:39

score 0 · Answer 1 · answered Mar 21 '14 at 05:41

0

Can you have multiple order_id and quantity pairs in table B? If so, I think what you want is this:

SELECT order_id FROM B GROUP BY order_id HAVING SUM(quantity) <= 15

answered Mar 21 '14 at 05:41

mjec

1,817
12
20

despite not joining table a & b, this query is returning every row <= 15. I am trying to figure out how to return rows, when added up the quantity is <=15 or whatever that number might be – user3400139 Mar 21 '14 at 06:39

Kirk Logan · Answer 2 · 2014-03-21T06:48:04.393

0

If order_id is unique in table B. (does not have multiple rows with same id) then use the following:

SELECT a.order_id, col1, col2, col3 
FROM TableA a
LEFT JOIN TableB b
ON a.order_id = b.order_id 
WHERE b.quantity <= {$magic_number}

If order_id is not unique in table B(has multiple rows with the same order_id) then use the following:

SELECT a.order_id, col1, col2, col3 
FROM TableA a
LEFT JOIN TableB b
ON a.order_id = b.order_id 
GROUP BY a.order_id 
HAVING SUM(b.quantity) <= {$magic_number}

edited Mar 21 '14 at 06:48

answered Mar 21 '14 at 06:40

Kirk Logan

733
2
8
23

the order id's are UNIQUE. What <= is doing in this query is returning ALL rows with quantity <= $magic_number. I am trying to return only rows: when added together TOTAL <= $magic number – user3400139 Mar 21 '14 at 07:04
Im having trouble understanding your GROUP BY criteria here... What determines which rows will be added together? Maybe post an example of the output youd like to see? When you say "I am trying to return only rows: when added together TOTAL <= $magic number" That is ambiguous. – Kirk Logan Mar 21 '14 at 16:28
the magic number is an order to be filled, the quantities in table b can be used to fill that order. So, if the magic number is 15, the most efficient quantities to fill that order is 5 & 6. These will be the two records the query should show – user3400139 Mar 21 '14 at 22:11
Ahh i see, so the "quantity" is being treated more like available stock in this case then. – Kirk Logan Mar 21 '14 at 22:23
yes, it is...if the order (magic number) was 30, the query will return the first two records with a total quantity of 30 – user3400139 Mar 22 '14 at 02:24

Order Matching Query

2 Answers2