質問編集履歴
2
タイトル編集
title
CHANGED
|
@@ -1,1 +1,1 @@
|
|
|
1
|
-
LINQ 複数テーブルjoinと複合条件とwhere
|
|
1
|
+
至急お願いします。LINQ 複数テーブルjoinと複合条件とwhere
|
body
CHANGED
|
File without changes
|
1
SQL文追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -62,6 +62,9 @@
|
|
|
62
62
|
[NAME_15],
|
|
63
63
|
[NAME_16],
|
|
64
64
|
|
|
65
|
+
|
|
66
|
+
|
|
67
|
+
|
|
65
68
|
|
|
66
69
|
|
|
67
70
|
|
|
@@ -86,6 +89,59 @@
|
|
|
86
89
|
NAME_ID = (HK.NAME_1 == "0" ? "" : from c in g select c.BCD)
|
|
87
90
|
};
|
|
88
91
|
|
|
92
|
+
|
|
93
|
+
|
|
94
|
+
|
|
95
|
+
SQLで書くとこうなります。
|
|
96
|
+
|
|
97
|
+
SELECT
|
|
98
|
+
[ID],
|
|
99
|
+
[DATE],
|
|
100
|
+
[NO],
|
|
101
|
+
[DSP_NO],
|
|
102
|
+
(CASE v1.[RTth] WHEN '0' THEN '' ELSE 'R'+v1.[RTth] END) +
|
|
103
|
+
(CASE v2.[LTth] WHEN '0' THEN '' ELSE 'L'+v2.[LTth] END) +
|
|
104
|
+
(CASE v3.[RTth] WHEN '0' THEN '' ELSE 'r'+v3.[RTth] END) +
|
|
105
|
+
(CASE v4.[LTth] WHEN '0' THEN '' ELSE 'l'+v4.[LTth] END) AS [BI],
|
|
106
|
+
ISNULL((CASE c1.[NAME_1] WHEN '0' THEN '' ELSE b1.[BCD] END) +
|
|
107
|
+
(CASE c1.[NAME_2] WHEN '0' THEN '' ELSE b2.[BCD] END) +
|
|
108
|
+
(CASE c1.[NAME_3] WHEN '0' THEN '' ELSE b3.[BCD] END) +
|
|
109
|
+
(CASE c1.[NAME_4] WHEN '0' THEN '' ELSE b4.[BCD] END) +
|
|
110
|
+
(CASE c1.[NAME_5] WHEN '0' THEN '' ELSE b5.[BCD] END) +
|
|
111
|
+
(CASE c1.[NAME_6] WHEN '0' THEN '' ELSE b6.[BCD] END) +
|
|
112
|
+
(CASE c1.[NAME_7] WHEN '0' THEN '' ELSE b7.[BCD] END) +
|
|
113
|
+
(CASE c1.[NAME_8] WHEN '0' THEN '' ELSE b8.[BCD] END) +
|
|
114
|
+
(CASE c2.[NAME_9] WHEN '0' THEN '' ELSE b9.[BCD] END) +
|
|
115
|
+
(CASE c2.[NAME_10] WHEN '0' THEN '' ELSE b10.[BCD] END) +
|
|
116
|
+
(CASE c2.[NAME_11] WHEN '0' THEN '' ELSE b11.[BCD] END) +
|
|
117
|
+
(CASE c2.[NAME_12] WHEN '0' THEN '' ELSE b12.[BCD] END) +
|
|
118
|
+
(CASE c2.[NAME_13] WHEN '0' THEN '' ELSE b13.[BCD] END) +
|
|
119
|
+
(CASE c2.[NAME_14] WHEN '0' THEN '' ELSE b14.[BCD] END) +
|
|
120
|
+
(CASE c2.[NAME_15] WHEN '0' THEN '' ELSE b15.[BCD] END) +
|
|
121
|
+
(CASE c2.[NAME_16] WHEN '0' THEN '' ELSE b16.[BCD] END) , '') AS [NAME_ID]
|
|
122
|
+
FROM Table3 AS k1
|
|
123
|
+
LEFT JOIN Table1 AS v1 ON k1.[UR] = v1.[NoFlg]
|
|
124
|
+
LEFT JOIN Table1 AS v2 ON k1.[UL] = v2.[NoFlg]
|
|
125
|
+
LEFT JOIN Table1 AS v3 ON k1.[DR] = v3.[NoFlg]
|
|
126
|
+
LEFT JOIN Table1 AS v4 ON k1.[DL] = v4.[NoFlg]
|
|
127
|
+
LEFT JOIN table4 AS c1 ON k1.[TOUROKU_1] = c1.[TOUROKU_NO]
|
|
128
|
+
LEFT JOIN Table2 AS b1 ON k1.[ID] = b1.[ID] AND c1.[NAME_1] = b1.[TOUROKU_NO] AND k1.[DATE] >= b1.[START_DATE] AND k1.[DATE] < b1.[END_DATE]
|
|
129
|
+
LEFT JOIN Table2 AS b2 ON k1.[ID] = b2.[ID] AND c1.[NAME_2] = b2.[TOUROKU_NO] AND k1.[DATE] >= b2.[START_DATE] AND k1.[DATE] < b2.[END_DATE]
|
|
130
|
+
LEFT JOIN Table2 AS b3 ON k1.[ID] = b3.[ID] AND c1.[NAME_3] = b3.[TOUROKU_NO] AND k1.[DATE] >= b3.[START_DATE] AND k1.[DATE] < b3.[END_DATE]
|
|
131
|
+
LEFT JOIN Table2 AS b4 ON k1.[ID] = b4.[ID] AND c1.[NAME_4] = b4.[TOUROKU_NO] AND k1.[DATE] >= b4.[START_DATE] AND k1.[DATE] < b4.[END_DATE]
|
|
132
|
+
LEFT JOIN Table2 AS b5 ON k1.[ID] = b5.[ID] AND c1.[NAME_5] = b5.[TOUROKU_NO] AND k1.[DATE] >= b5.[START_DATE] AND k1.[DATE] < b5.[END_DATE]
|
|
133
|
+
LEFT JOIN Table2 AS b6 ON k1.[ID] = b6.[ID] AND c1.[NAME_6] = b6.[TOUROKU_NO] AND k1.[DATE] >= b6.[START_DATE] AND k1.[DATE] < b6.[END_DATE]
|
|
134
|
+
LEFT JOIN Table2 AS b7 ON k1.[ID] = b7.[ID] AND c1.[NAME_7] = b7.[TOUROKU_NO] AND k1.[DATE] >= b7.[START_DATE] AND k1.[DATE] < b7.[END_DATE]
|
|
135
|
+
LEFT JOIN Table2 AS b8 ON k1.[ID] = b8.[ID] AND c1.[NAME_8] = b8.[TOUROKU_NO] AND k1.[DATE] >= b8.[START_DATE] AND k1.[DATE] < b8.[END_DATE]
|
|
136
|
+
LEFT JOIN table5 AS c2 ON k1.[TOUROKU_2] = c2.[TOUROKU_NO]
|
|
137
|
+
LEFT JOIN Table2 AS b9 ON k1.[ID] = b9.[ID] AND c2.[NAME_9] = b9.[TOUROKU_NO] AND k1.[DATE] >= b9.[START_DATE] AND k1.[DATE] < b9.[END_DATE]
|
|
138
|
+
LEFT JOIN Table2 AS b10 ON k1.[ID] = b10.[ID] AND c2.[NAME_10] = b10.[TOUROKU_NO] AND k1.[DATE] >= b10.[START_DATE] AND k1.[DATE] < b10.[END_DATE]
|
|
139
|
+
LEFT JOIN Table2 AS b11 ON k1.[ID] = b11.[ID] AND c2.[NAME_11] = b11.[TOUROKU_NO] AND k1.[DATE] >= b11.[START_DATE] AND k1.[DATE] < b11.[END_DATE]
|
|
140
|
+
LEFT JOIN Table2 AS b12 ON k1.[ID] = b12.[ID] AND c2.[NAME_12] = b12.[TOUROKU_NO] AND k1.[DATE] >= b12.[START_DATE] AND k1.[DATE] < b12.[END_DATE]
|
|
141
|
+
LEFT JOIN Table2 AS b13 ON k1.[ID] = b13.[ID] AND c2.[NAME_13] = b13.[TOUROKU_NO] AND k1.[DATE] >= b13.[START_DATE] AND k1.[DATE] < b13.[END_DATE]
|
|
142
|
+
LEFT JOIN Table2 AS b14 ON k1.[ID] = b14.[ID] AND c2.[NAME_14] = b14.[TOUROKU_NO] AND k1.[DATE] >= b14.[START_DATE] AND k1.[DATE] < b14.[END_DATE]
|
|
143
|
+
LEFT JOIN Table2 AS b15 ON k1.[ID] = b15.[ID] AND c2.[NAME_15] = b15.[TOUROKU_NO] AND k1.[DATE] >= b15.[START_DATE] AND k1.[DATE] < b15.[END_DATE]
|
|
144
|
+
LEFT JOIN Table2 AS b16 ON k1.[ID] = b16.[ID] AND c2.[NAME_16] = b16.[TOUROKU_NO] AND k1.[DATE] >= b16.[START_DATE] AND k1.[DATE] < b16.[END_DATE]
|
|
89
145
|
```
|
|
90
146
|
|
|
91
147
|
###試したこと
|