如何为多个DataTable创建Query语法来实现Sql Server的IN运算符 [英] How to create Query syntax for multiple DataTable for implementing IN operator of Sql Server
问题描述
我必须为多个表单维护此数据集，并且我没有在此数据集上执行任何DML操作。
现在，此数据集包含4个表，我必须提取一些记录来显示数据。
存储在表中的数据是一对多关系的形式。
在交易的情况下。每记录N条记录。那么这些N条记录进一步映射到第3表的M条记录。
表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
表2
Department_ID Department_Name Parent_Id Parent_Name
-------------------- ----------------------- --- ------------ -------------------------------------- --------------------------------------------
1食物1，5，6食品，北印度，南印度
2住宿3，4，13，20，23房间，无空调，带阳台，无阳台
40新40子新
表3
Parent_Id Parent_Name Preference_ID Preference_Name
-------------------- - --------------------------------------------- ----- ------------------ -------------------
NULL NULL NULL NULL
1食物5,8北印度，意大利语
3房间4楼
4楼9，10，11第一，第二，第三
5北印度14，15 X，Y
6南印度16多萨
13非空调22，23带阳台，无阳台
20带阳台24,25,26山景，海景，花园查看
23无阳台27山景
40新41子新
我有这3个表格是以这种方式相关的。
表1将是这两个表格的主表，即表2和表3。
我需要查询他们作为
SELECT Department_Id，Department_Name，Parent_Name FROM Table2 WHERE Departme
中的nt_Id（
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
）
现在我需要在DataTables上使用这些查询。
所以我正在使用查询语法，达到这个目的。
在$ d code $ c $ var dept_list = from dept在DtMapGuestDepartment.AsEnumerable（）
其中dept.Field< long>（PK_GUEST_ID）== long.Parse（63）
选择部门;
这应该给我所有拥有guest id = 63的部门的列表
现在我想从表2中选择所有的department_name和parent_name，其中guest_id = 63即我上面提到的部门。
同样的情况将遵循表3。
请建议如何做到这一点。
感谢您的耐心阅读我的问题。
嗯，假设你有3个IEnumerable var，每个表一个。您的第一个查询将是这样的：
var records = from table1中的r1，其中r1.GuestId == 63
在r1上的table2中加入r2.DepartmentId等于r2.DepartmentId
select r2;
第二个查询与第一个查询几乎相同，加入表3而不是2.
var records = from table1中的r1，其中r1.GuestId == 63
在r3上的表3中加入r3。 r2.ParentId
选择r3;
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.
Well, suppose you have 3 IEnumerable vars, one for each table. Your first query would be like this:
var records = from r1 in table1 where r1.GuestId == 63
join r2 in table2 on r1.DepartmentId equals r2.DepartmentId
select r2;
The second query is nearly the same as the first one, joining with table 3 instead of 2.
var records = from r1 in table1 where r1.GuestId == 63
join r3 in table3 on r1.ParentId equals r2.ParentId
select r3;
这篇关于如何为多个DataTable创建Query语法来实现Sql Server的IN运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
