Mehdi Rostami Mehdi Rostami - 1 month ago 15
SQL Question

Select distinct duplicated rows from 1:n tables in SQL Server


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.

I tried
DISTINCT
,
GROUP BY
but can't do it.


SQL:

select *
from Customer, Order
where Order.CustomerID = Customer.CustomerID
and Customer.CustomerID = '2'


Tables:

Tables view

Answer

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.