SQL Query in c#

Question

I might have a problem with my SQL query. In this query I'm combining 4 different tables.

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 a job_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 the job_id and the course_id).
If a employee completed a course it is stored in the table emp_courses (with the e_id and the course_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

Here it is:

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 + "'");

Can someone please help me with this?

shudder - don't concatenate input like that; please use where course_number like @coursenumber and add a parameter called @coursenumber — Marc Gravell
– Marc Gravell, Commented Aug 1, 2012 at 8:59

Mike Perrenoud · Accepted Answer · 2012-08-01 09:14:14Z

1

Courses the employee hasn't taken.

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}
)

Variant of above.

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}
    )
)

edited Aug 1, 2012 at 9:14

answered Aug 1, 2012 at 8:58

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Meilenea Over a year ago

what do you mean with the "someid" ... in your query i don't have the possibility to compare the stored course_numbers to the one that the users types in .. ( I should have mentioned that : The user has the possibility to type in the cours_number and when he presses the search button, it will be stored : string coursenumber=this.textBox1.Text; )

Mike Perrenoud Over a year ago

So the value that's typed in goes in the place of that. I couldn't put anything there because it has to be supplied. There are many different ways to query your database from a lot of different angles but I think the second query is exactly what you're looking for. Plug the users input in there and get the employees that haven't taken that specific course.

Meilenea Over a year ago

oh but the course_id is not the course_number .. so i also need the course table... but besides that your query looks good !

Mike Perrenoud Over a year ago

@meilenea If this answered it please mark it as the answer by clicking the check box just below the voting system. Thanks!

Meilenea Over a year ago

Oh thanks for the last one ! no i just need to make on mor comparision ... but i think i can make it on my own ;)

Collectives™ on Stack Overflow

SQL Query in c#

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related