Selecting rows from table according the rows from another table

[Zoltan Hubai]

Hi

I would like to select rows from one table according the rows from
another table in .net with C#. Both tables are loaded into memory.
For example a simple structure

CustomerGroup Customer
------------- --------
groupID customerID
name name
customerID (FK)
employee (FK)


Now according wich employee is loged in his customer groups are filtered
and after he selects the group i would like to filter the rows in the
customer table.
Somthing like:

DataRow[] rows =
CustomerGroup.Select("groupID=\""+employee_Selected_Group+"\"");
Customer.Select("customerID in rows.customerID").

Only to get an idea what I intend to do, no casting etc. in example.
Not sure if this is possible.
I could do the same with SQL but then when the employee changes the
group I would need to refetch again the records. In SQL then following
should be like:

SELECT * FROM Customer WHERE Customer.customerID IN (SELECT
CustomerGroup.customerID FROM CustomerGroup WHERE
CustomerGroup.groupID=:GROUPID)

Thx, and sorry for my poor english

 

[murali.trichy]

// in C#
foreach(datarow CGrow in
CustomerGroup.select("GroupID='"+employee_Selected_Group+"'))
{
foreach(datarow Crow in Customer.select("CustomerID = '" +
CGrow["CustomerID"].toString() +"'"))
{
// do u'r process for that employees
}
}

I think this is what you expect !

 

[Zoltan Hubai]

// in C#
foreach(datarow CGrow in
CustomerGroup.select("GroupID='"+employee_Selected_Group+"'))
{
foreach(datarow Crow in Customer.select("CustomerID = '" +
CGrow["CustomerID"].toString() +"'"))
{
// do u'r process for that employees
}
}

I think this is what you expect !

Thx for your answer

Yes, partialy it is. I tought there is a more simple way that will
return a rows collection of the customers so that it can be simply
assigned to a datagrid.
Actually there is no need for the second foreach loop as inside one
group the customerID only once can apear.

It could be more like
CustomerTable groupedCustomer = new CustomerTable();
groupedCustomer.AddCustomerRow(Customer.Select("CustomerID='"+CGrow["CustomerID"].ToString()+"'"));

And after I can assign CustomerTable to a dataGrid.