sql query in ireport

[nazish zehra]

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

ORDER:Order_ID, Seed_ID,quantit y(here orderid and seedid are composite primary key and one order may contain requests of multiple seeds by the same requester)
ORDER_LINE:Orde r_ID,Req_ID,Dat e 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:

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;

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.

[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:

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;

[nazish zehra]

No i dont want total quantity .Thanku for reply.Got it solved and sorry for inconvenience

[nazish zehra]

Can anyone tell me about basic tutorial on charts in ireport