i have two data tables namely profitloss and delivery, the query for each are as follows:
SELECT customer.name, SUM([Milk Delivery].Paid) AS Paid
FROM customer INNER JOIN
[Milk Delivery] ON customer.Code = [Milk Delivery].Code
WHERE ([Milk Delivery].Date BETWEEN @id AND @id2)
GROUP BY customer.name
here customer is a different table which helps to know the customer name using the code. If a certain date range is being entered using textbox or similar input, it groups the output using customer name and shows the total amount paid for each customer within the timeperiod.
profitloss adapter is as follows:
SELECT id, date, item, supplier, qty, amt, paid, due
WHERE (date BETWEEN @id AND @id2)
ORDER BY date
my question is how can i show these together without using sub report, in such a manner that, the results would come up like a full outer join, as there is no dependencies with these two, and show them in crystal report? i already included them in a new dataset and have set the same as the data source for my crystal report, also have included these two tables in crystal report,
but in result crystal report shows repeated rows, just like a cross join or cartesian product, which i do not want to see, instead i want the result to show just like the result of a full outer join with null rows removed! please help!
furthermore, if anyone can show me how to put these two together in a single adapter using full outer join, it would be same helpful for me.