How to create Query syntax for multiple DataTable for implementing IN operator of Sql Server
- by Shantanu Gupta
I have fetched 3-4 tables by executing my stored procedure. Now they resides on my dataset.
I have to maintain this dataset for multiple forms and I am not doing any DML operation on this dataset.
Now this dataset contains 4 tables out of which i have to fetch some records to display data.
Data stored in tables are in form of one to many relationship.
i.e. In case of transactions. N records per record. Then these N records are further mapped to M records of 3rd table.
Table 1
MAP_ID GUEST_ID DEPARTMENT_ID PARENT_ID PREFERENCE_ID
-------------------- -------------------- -------------------- -------------------- --------------------
19 61 1 1 5
14 61 1 5 15
15 61 2 4 10
18 61 2 13 23
17 61 2 20 26
16 61 40 40 41
20 62 1 5 14
21 62 1 5 15
22 62 1 6 16
24 62 2 3 4
23 62 2 4 9
27 62 2 13 23
25 62 2 20 24
26 62 2 20 25
28 63 1 1 5
29 63 1 1 8
34 63 1 5 15
30 63 2 4 10
33 63 2 4 11
31 63 2 13 23
32 63 40 40 41
35 65 1 NULL 1
36 65 1 NULL 1
38 68 2 13 22
37 68 2 20 25
39 68 2 23 27
40 92 1 NULL 1
Table 2
Department_ID Department_Name Parent_Id Parent_Name
-------------------- ----------------------- --------------- ----------------------------------------------------------------------------------
1 Food 1, 5, 6 Food, North Indian, South Indian
2 Lodging 3, 4, 13, 20, 23 Room, Floor, Non Air Conditioned, With Balcony, Without Balcony
40 New 40 SubNew
TABLE 3
Parent_Id Parent_Name Preference_ID Preference_Name
-------------------- ----------------------------------------------- ----------------------- -------------------
NULL NULL NULL NULL
1 Food 5, 8 North Indian, Italian
3 Room 4 Floor
4 Floor 9, 10, 11 First, Second, Third
5 North Indian 14, 15 X, Y
6 South Indian 16 Dosa
13 Non Air Conditioned 22, 23 With Balcony, Without Balcony
20 With Balcony 24, 25, 26 Mountain View, Ocean View, Garden View
23 Without Balcony 27 Mountain View
40 New 41 SubNew
I have these 3 tables that are related in some fashion like this.
Table 1 will be the master for these 2 tables i.e. table 2 and table 3.
I need to query on them as
SELECT Department_Id, Department_Name, Parent_Name FROM Table2 WHERE Department_Id in
(
SELECT Department_Id FROM Table1 WHERE guest_id=65
)
SELECT Parent_Id, Parent_Name, Preference_Name FROM Table3 WHERE PARENT_ID in
(
SELECT parent_id FROM Table1 WHERE guest_id=65
)
Now I need to use these queries on DataTables.
So I am using Query Syntax for this and reached up to this point.
var dept_list= from dept in DtMapGuestDepartment.AsEnumerable()
where dept.Field<long>("PK_GUEST_ID")==long.Parse(63)
select dept;
This should give me the list of all departments that has guest id =63
Now I want to select all departments_name and parent_name from Table 2 where guest_id=63 i.e. departments that i fetched above.
This same case will be followed for Table3.
Please suggest how to do this.
Thanks for keeping up patience for reading my question.
