質問編集履歴
2
タイトル編集
test
CHANGED
|
@@ -1 +1 @@
|
|
|
1
|
-
LINQ 複数テーブルjoinと複合条件とwhere
|
|
1
|
+
至急お願いします。LINQ 複数テーブルjoinと複合条件とwhere
|
test
CHANGED
|
File without changes
|
1
SQL文追加
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -126,6 +126,12 @@
|
|
|
126
126
|
|
|
127
127
|
|
|
128
128
|
|
|
129
|
+
|
|
130
|
+
|
|
131
|
+
|
|
132
|
+
|
|
133
|
+
|
|
134
|
+
|
|
129
135
|
|
|
130
136
|
|
|
131
137
|
|
|
@@ -174,6 +180,112 @@
|
|
|
174
180
|
|
|
175
181
|
|
|
176
182
|
|
|
183
|
+
|
|
184
|
+
|
|
185
|
+
|
|
186
|
+
|
|
187
|
+
|
|
188
|
+
|
|
189
|
+
SQLで書くとこうなります。
|
|
190
|
+
|
|
191
|
+
|
|
192
|
+
|
|
193
|
+
SELECT
|
|
194
|
+
|
|
195
|
+
[ID],
|
|
196
|
+
|
|
197
|
+
[DATE],
|
|
198
|
+
|
|
199
|
+
[NO],
|
|
200
|
+
|
|
201
|
+
[DSP_NO],
|
|
202
|
+
|
|
203
|
+
(CASE v1.[RTth] WHEN '0' THEN '' ELSE 'R'+v1.[RTth] END) +
|
|
204
|
+
|
|
205
|
+
(CASE v2.[LTth] WHEN '0' THEN '' ELSE 'L'+v2.[LTth] END) +
|
|
206
|
+
|
|
207
|
+
(CASE v3.[RTth] WHEN '0' THEN '' ELSE 'r'+v3.[RTth] END) +
|
|
208
|
+
|
|
209
|
+
(CASE v4.[LTth] WHEN '0' THEN '' ELSE 'l'+v4.[LTth] END) AS [BI],
|
|
210
|
+
|
|
211
|
+
ISNULL((CASE c1.[NAME_1] WHEN '0' THEN '' ELSE b1.[BCD] END) +
|
|
212
|
+
|
|
213
|
+
(CASE c1.[NAME_2] WHEN '0' THEN '' ELSE b2.[BCD] END) +
|
|
214
|
+
|
|
215
|
+
(CASE c1.[NAME_3] WHEN '0' THEN '' ELSE b3.[BCD] END) +
|
|
216
|
+
|
|
217
|
+
(CASE c1.[NAME_4] WHEN '0' THEN '' ELSE b4.[BCD] END) +
|
|
218
|
+
|
|
219
|
+
(CASE c1.[NAME_5] WHEN '0' THEN '' ELSE b5.[BCD] END) +
|
|
220
|
+
|
|
221
|
+
(CASE c1.[NAME_6] WHEN '0' THEN '' ELSE b6.[BCD] END) +
|
|
222
|
+
|
|
223
|
+
(CASE c1.[NAME_7] WHEN '0' THEN '' ELSE b7.[BCD] END) +
|
|
224
|
+
|
|
225
|
+
(CASE c1.[NAME_8] WHEN '0' THEN '' ELSE b8.[BCD] END) +
|
|
226
|
+
|
|
227
|
+
(CASE c2.[NAME_9] WHEN '0' THEN '' ELSE b9.[BCD] END) +
|
|
228
|
+
|
|
229
|
+
(CASE c2.[NAME_10] WHEN '0' THEN '' ELSE b10.[BCD] END) +
|
|
230
|
+
|
|
231
|
+
(CASE c2.[NAME_11] WHEN '0' THEN '' ELSE b11.[BCD] END) +
|
|
232
|
+
|
|
233
|
+
(CASE c2.[NAME_12] WHEN '0' THEN '' ELSE b12.[BCD] END) +
|
|
234
|
+
|
|
235
|
+
(CASE c2.[NAME_13] WHEN '0' THEN '' ELSE b13.[BCD] END) +
|
|
236
|
+
|
|
237
|
+
(CASE c2.[NAME_14] WHEN '0' THEN '' ELSE b14.[BCD] END) +
|
|
238
|
+
|
|
239
|
+
(CASE c2.[NAME_15] WHEN '0' THEN '' ELSE b15.[BCD] END) +
|
|
240
|
+
|
|
241
|
+
(CASE c2.[NAME_16] WHEN '0' THEN '' ELSE b16.[BCD] END) , '') AS [NAME_ID]
|
|
242
|
+
|
|
243
|
+
FROM Table3 AS k1
|
|
244
|
+
|
|
245
|
+
LEFT JOIN Table1 AS v1 ON k1.[UR] = v1.[NoFlg]
|
|
246
|
+
|
|
247
|
+
LEFT JOIN Table1 AS v2 ON k1.[UL] = v2.[NoFlg]
|
|
248
|
+
|
|
249
|
+
LEFT JOIN Table1 AS v3 ON k1.[DR] = v3.[NoFlg]
|
|
250
|
+
|
|
251
|
+
LEFT JOIN Table1 AS v4 ON k1.[DL] = v4.[NoFlg]
|
|
252
|
+
|
|
253
|
+
LEFT JOIN table4 AS c1 ON k1.[TOUROKU_1] = c1.[TOUROKU_NO]
|
|
254
|
+
|
|
255
|
+
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]
|
|
256
|
+
|
|
257
|
+
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]
|
|
258
|
+
|
|
259
|
+
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]
|
|
260
|
+
|
|
261
|
+
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]
|
|
262
|
+
|
|
263
|
+
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]
|
|
264
|
+
|
|
265
|
+
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]
|
|
266
|
+
|
|
267
|
+
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]
|
|
268
|
+
|
|
269
|
+
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]
|
|
270
|
+
|
|
271
|
+
LEFT JOIN table5 AS c2 ON k1.[TOUROKU_2] = c2.[TOUROKU_NO]
|
|
272
|
+
|
|
273
|
+
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]
|
|
274
|
+
|
|
275
|
+
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]
|
|
276
|
+
|
|
277
|
+
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]
|
|
278
|
+
|
|
279
|
+
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]
|
|
280
|
+
|
|
281
|
+
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]
|
|
282
|
+
|
|
283
|
+
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]
|
|
284
|
+
|
|
285
|
+
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]
|
|
286
|
+
|
|
287
|
+
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]
|
|
288
|
+
|
|
177
289
|
```
|
|
178
290
|
|
|
179
291
|
|