Hi,
[EMAIL PROTECTED] schrieb:
Hi,
I have 2 tables as follows:
table 1 contains code,order_qty
table 2 contains code,stock_qty
table 1:
code1, 10
code2, 2
code3, 5
table 2:
code1, 3
code3, 5
code1, 4
code3, 2
I need to see the following result:
code | order_qty| stock_qty
code1 | 10 | 7
code2 | 2 | 0
The condition is : order_qty >sum(stock_qty) and note that if code is
not found in table2, stock_qty is 0.
Can this be achieved with a single select query? or suggest the best
option.
mysql> create table table1 ( code int, order_qty int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table table2 ( code int, stock_qty int);
Query OK, 0 rows affected (0.23 sec)
mysql> insert into table1 values (1,10),(2,2),(3,5);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into table2 values (1,3),(3,5),(1,4),(3,2);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select table1.code,order_qty,coalesce(sum(stock_qty),0)
from table1
left join table2 using(code)
group by table1.code
having order_qty > coalesce(sum(stock_qty),0);
+------+-----------+----------------------------+
| code | order_qty | coalesce(sum(stock_qty),0) |
+------+-----------+----------------------------+
| 1 | 10 | 7 |
| 2 | 2 | 0 |
+------+-----------+----------------------------+
2 rows in set (0.02 sec)
mysql>
Ciao,
Thomas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
