i have the follwing table sructures
cust_inf(cust_id,cust_name,address,contactperson)
orders(order_no,cust_id,product_name,order_qty,order_amount,order_date)
invoices(inv_key,order_no,inv_date,cust_id,order_date,productname,inv_qty,inv_amount,Lr_no,
despatch_thr)
sample data is
cust_info
---------
<pre lang="SQL">
cust_id|cust_name|adress |contactperson
---------------------------------------
1 | xxx | aaa |vvv
2 | yyy | bbb |nnn
---------------------------------------
orders
-------
lang="SQL"></pre>
+----------+---------+---------------------------+-----------+--------------+-----------
| order_no | cust_id | product_name | order_qty | order_amount |order_date
+----------+---------+---------------------------+-----------+--------------+-----------<pre
| o1 | 2 | Provel 200gms Powder | 12 | 456 | 2013-11-06
| o1 | 2 | Calcy - D 10X2X10 | 15 | 810 | 2013-11-06
| o4 | 2 | Obact-200mg 10 X10 Tab | 8 | 960 | 2013-11-06
| o4 | 2 | Obact-OZ 10 X10 Tab | 7 | 1050 | 2013-11-06
| o4 | 2 | Omcid - D 10X15 | 12 | 1320 | 2013-11-06
| o2 | 1 | Pragmox -250mg 20 X10 Cap | 10 | 2330 | 2013-11-07
| o2 | 1 | Provel 200gms Powder | 15 | 465 | 2013-11-07
| o2 | 1 | Obact-200mg 10 X10 Tab | 12 | 1320 | 2013-11-07
| o2 | 1 | Obact-400mg 10 X10 Tab | 8 | 1872 | 2013-11-07
| o2 | 1 | Obact-OZ 10 X10 Tab | 7 | 1260 | 2013-11-08
| o2 | 1 | Omcid - D 10X15 | 12 | 1224 | 2013-11-07
| o3 | 3 | Provel 200gms Powder | 88 | 777 | 2013-11-21
+----------+---------+---------------------------+-----------+--------------+-----------
invoices
--------
+
| inv_key | order_no | inv_date | cust_id | order_date |
+
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o1 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1423 | o3 | 2013-11-21 | 3 | 2013-11-21 |
| EDPL-1253 | o1 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1254 | o4 | 2013-11-19 | 2 | 2013-11-06 |
+
+
| productname | inv_qty | inv_amount |
+
| Omcid - D 10X15 | 5 | 1000 |
| Obact-OZ 10 X10 Tab | 7 | 900 |
| Obact-200mg 10 X10 Tab | 8 | 720 |
| Calcy - D 10X2X10 | 15 | 540 |
| Pragmox -250mg 20 X10 Cap | 8 | 1864 |
| Provel 200gms Powder | 12 | 372 |
| Obact-200mg 10 X10 Tab | 12 | 1320 |
| Obact-400mg 10 X10 Tab | 7 | 1638 |
| Obact-OZ 10 X10 Tab | 7 | 1260 |
| Omcid - D 10X15 | 12 | 1224 |
| Provel 200gms Powder | 88 | 777 |
| Provel 200gms Powder | 12 | 540 |
| Omcid - D 10X15 | 7 | 320 |
+
my requirement is get the all cust related order details and invoice details like
my query is
select cust_name,address,contactperson,phone,
group_concat(orders.order_no order by orders.order_no asc) as order_no,
group_concat(product_name) As or_product,
group_concat(order_qty) As order_qty ,
group_concat(if(inv_qty is null,0,inv_qty)) as inv_qty,
group_concat(orders.order_date) As order_date from cust_info
join orders on cust_info.cust_id=orders.cust_id
left outer join invoices on cust_info.cust_id=invoices.cust_id
and orders.product_name=invoices.productname and orders.order_date=invoices.order_date
group by cust_name,orders.order_no;
result is
(cust_name, address, contactperson, phone, order_no, or_product, order_qty, inv_qty, order_date) VALUES
(' Srinnivas Biotech', '45-120/94, Manju shanker Aparments,Bblock', 'Mr. Shiva Kumar', '9883883883', 'o3', 'Provel 200gms Powder', '88', '88', '2013-11-21'),
('M/s Sri baba Enterprices', '#11-13-23,Sammetavari street,Near ramanaiah cool drink shop,Vijayawada-520001', 'Mr.Prasad', '9346560014', 'o2,o2,o2,o2,o2,o2', 'Provel 200gms Powder,Pragmox -250mg 20 X10 Cap,Omcid - D 10X15,Obact-OZ 10 X10 Tab,Obact-400mg 10 X10 Tab,Obact-200mg 10 X10 Tab', '15,10,12,7,8,12', '12,8,12,0,7,12', '2013-11-07,2013-11-07,2013-11-07,2013-11-08,2013-11-07,2013-11-07'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o1,o1', 'Calcy - D 10X2X10,Provel 200gms Powder', '15,12', '15,12', '2013-11-06,2013-11-06'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o4,o4,o4,o4', 'Obact-200mg 10 X10 Tab,Omcid - D 10X15,Omcid - D 10X15,Obact-OZ 10 X10 Tab', '8,12,12,7', '8,7,5,7', '2013-11-06,2013-11-06,2013-11-06,2013-11-06')
in or_product field contains same product name, if product name name is same then add their inv_qty inside the group concat(add inv_qty then group concat) but it is not working for me. i used the following query;
select cust_name,address,contactperson,phone,
group_concat(orders.order_no order by orders.order_no asc) as order_no,
group_concat(product_name) As or_product,
group_concat(order_qty) As order_qty ,
group_concat(case when count(invoices.productname)>1 then sum(ifnull(inv_qty,0) else inv_qty end) as inv_qty,
group_concat(orders.order_date) As order_date from cust_info
join orders on cust_info.cust_id=orders.cust_id
left outer join invoices on cust_info.cust_id=invoices.cust_id
and orders.product_name=invoices.productname and orders.order_date=invoices.order_date
group by cust_name,orders.order_no
without using group by invoices.productname i need records. is there anyway to get the correct records please help me.
Thanks in advance