How to query from multiple datatables?

[NobleJms]

Hi guys, I’d greatly appreciate if someone could help me with this or give me some pointers. I am trying to create a stock scanner which allows users to filter stocks based on multiple criteria that they select.

Like the following example with 2 criteria:

Criteria 1 Filtered Result: StockA, StockB, StockC, StockD
Criteria 2 Filtered Result: Stock B, Stock C, StockE, StockF

SCANNER RESULT: Stock B, StockC (ideally this result would be displayed in a GridView)

Now this seems like an easy task for a simple SQL JOIN statement, but the problem is that the SQL statements I’ve written for some criteria are already so complex that it’s unthinkable to combine them further by using a SQL JOIN statement.

I think the only way to accomplish this is to put each criteria-filtered result into an array or datatable, and then try to query to see which stocks exist in all the arrays/datatable (in other words satisfy all criteria), and to display these stocks in a gridview. After doing some research online, it is possible to do query from multiple datatables by either using:

JOINVIEW(DataRelation) or LINQ

But which one is the best method (if it works)? I didn’t consider using a loop for this since it would be too inefficient, especially if a user select many criteria.

I’d greatly appreciate if someone could give me some suggestions or simple syntax I can use to achieve this. Thanks in advance!

[TechGnome]

I would probably go for LINQ... depends on the complexity... What you'll need to look for is the GroupJoin function in LINQ... I think that's the right one. Had to do something similar to this once. If I remember right, it involved creating a list that contained all of the distinct items, then using that list to filter out from the other where items were contained in the list. With some care, it can be done all at once.

Also look up INTERSECT ... I can't remember just how it's used but there is a function that you can call on one, feeding it the other and it will return rows that are in common. It may be an array extension method though. But it's at least worth looking up.

-tg