C#中的SQL查询 [英] SQL Query in c#
本文介绍了C#中的SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的SQL查询可能有问题.在此查询中,我合并了4个不同的表.
I might have a problem with my SQL query. In this query I'm combining 4 different tables.
- 我有一个表
courses
,用于存储常规信息(course_number
,course_title
). - 我有一个表
employees
,用于存储员工的一般信息(empname
和job_id
). - 员工有工作.一名员工需要参加课程.这取决于他必须修的课程.此信息存储在表
job_course
(以及job_id
和course_id
)中. - 如果员工完成了课程,则将其存储在表
emp_courses
中(包含e_id
和course_id
)
- I have a table
courses
where general information is stored (course_number
,course_title
). - I have a table
employees
where general information of employees isstored (empname
, and ajob_id
). - A employee has a job. A employee needs to take courses. It depends on the job which courses he has to take. This info is stored in the table
job_course
(with thejob_id
and thecourse_id
). - If a employee completed a course it is stored in the table
emp_courses
(with thee_id
and thecourse_id
)
现在,我想搜索某个课程-当用户按下搜索按钮时,他应该得到两个不同的结果.
Now I want to search a certain course - when the user presses the search button he should get two different results.
- 第一个:在这里您可以看到哪个员工已经参加了这门课程(该查询到目前为止有效)
- 第二个:在这里您可以看到哪个员工仍需要参加该课程.因此,我需要检查员工所从事的工作以及他是否需要参加该课程.而且我也只想拥有尚未完成的内容. 那是不起作用的查询
- The first one: here you can see which employee already took this course (this query works so far)
- the second one: here you can see which employee still needs to take the course. So i need to check which job the employee has and if he needs to make that course . and also i just want to have the ones that are not completed yet. And that's the query that is not working
这里是:
OpenDb_Open("select course_number,course_title, empname from course
INNER JOIN (job_course INNER JOIN (employee INNER JOIN emp_course
ON emp_course.e_id<>employee.e_id) ON job_course.job_id=employee.job_id)
ON course.course_id=job_course.course_id
where course_number like '" + coursenumber + "'");
有人可以帮我吗?
推荐答案
员工未参加的课程.
SELECT * FROM courses
WHERE course_number IN (
SELECT course_id FROM job_course
WHERE course_id NOT IN (
SELECT course_id FROM emp_courses
WHERE emp_id = {someid}
) AND job_id = (
SELECT job_id FROM employees
WHERE emp_id = {user_input}
)
)
哪些员工仍需要参加课程.
Which employees still need to take a course.
SELECT emp_name FROM employees
WHERE emp_id NOT IN (
SELECT emp_id FROM emp_courses
WHERE course_id = {user_input}
)
以上版本.
SELECT emp_name FROM employees
WHERE emp_id NOT IN (
SELECT emp_id FROM emp_courses
WHERE course_id = (
SELECT course_id FROM courses
WHERE course_number = {user_input}
)
)
这篇关于C#中的SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文