By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,040 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,040 IT Pros & Developers. It's quick & easy.

sql query in ireport

P: 31
I am using a sql query in ireport to make a report based on two tables.I have 2 tables ORDER and ORDER_LINE.Orders contains orders of seeds ordered by different requesters.

ORDER:Order_ID,Seed_ID,quantity(here orderid and seedid are composite primary key and one order may contain requests of multiple seeds by the same requester)
ORDER_LINE:Order_ID,Req_ID,Date of order,no.of seeds

Now i have to show all this information on a single report.
I am using ireport and i m new to this tool.The query i have made is like this:
Expand|Select|Wrap|Line Numbers
  1. Select orders.Order_ID AS "Order_ID",orders.quantity AS "quantity",orders.Seed_ID AS "Seed_ID",order_line.R_ID AS "R_ID",order_line.Date_order AS "date",order_line.no_of_seeds AS "no. of seeds",requesters.R_NAME AS "Req" from orders,order_line,requesters where orders.Order_ID=order_line.Order_ID AND order_line.R_ID=requesters.R_ID;
  2.  
The query is working but i want to display the Seed_ID's 0f one order separated by comma .I have use group_concat() but it shows then only one order and displays all the seed id's form the table in one row separated by comma.I need help for this query.I think my query for group_concat is not right.
Jun 22 '12 #1

✓ answered by Luuk

The query is working but i want to display the Seed_ID's 0f one order separated by comma
You can only do this if you use 'GROUP BY Order_ID'

But how will you show 'quantity' for this seeds?
Should it show the total quantity?

BTW, if you format your query its more readible:
Expand|Select|Wrap|Line Numbers
  1. Select 
  2.     orders.Order_ID AS "Order_ID",
  3.     orders.quantity AS "quantity",
  4.     orders.Seed_ID AS "Seed_ID",
  5.     order_line.R_ID AS "R_ID",
  6.     order_line.Date_order AS "date",
  7.     order_line.no_of_seeds AS "no. of seeds",
  8.     requesters.R_NAME AS "Req" 
  9. from orders,order_line,requesters 
  10. where 
  11.     orders.Order_ID=order_line.Order_ID 
  12.     AND order_line.R_ID=requesters.R_ID; 
  13.  
  14.  

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,035
The query is working but i want to display the Seed_ID's 0f one order separated by comma
You can only do this if you use 'GROUP BY Order_ID'

But how will you show 'quantity' for this seeds?
Should it show the total quantity?

BTW, if you format your query its more readible:
Expand|Select|Wrap|Line Numbers
  1. Select 
  2.     orders.Order_ID AS "Order_ID",
  3.     orders.quantity AS "quantity",
  4.     orders.Seed_ID AS "Seed_ID",
  5.     order_line.R_ID AS "R_ID",
  6.     order_line.Date_order AS "date",
  7.     order_line.no_of_seeds AS "no. of seeds",
  8.     requesters.R_NAME AS "Req" 
  9. from orders,order_line,requesters 
  10. where 
  11.     orders.Order_ID=order_line.Order_ID 
  12.     AND order_line.R_ID=requesters.R_ID; 
  13.  
  14.  
Jun 22 '12 #2

P: 31
No i dont want total quantity .Thanku for reply.Got it solved and sorry for inconvenience
Jun 22 '12 #3

P: 31
Can anyone tell me about basic tutorial on charts in ireport
Jun 22 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.