I am trying to select customers and their orders in one query, but I get customer and his orders in datatable which customer table columns repeated for each order.
DISTINCTbut can't do it.
from Customer, Order
where Order.CustomerID = Customer.CustomerID
and Customer.CustomerID = '2'
Since there cannot be different columns for each row you can't do it without having duplicates. Consider reading data separately, once for the customer and once for her orders.
i want to get all customers and orders the query count will grow.if i have 3 customer i want to get orders and customers in one query.not 6 times query execution.
You do not need to perfrom a separate query for each customer. You just need a single query for all customers and a single query for all orders. Then you may connect them in application layer rather than a single query.
But if you argue that you have too many customers and too many orders to hold them all in memory, well, then you may perform a separate query for each customer. That's a tradeoff between memory and CPU.