sql request that doesn't return which it's expected to return

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have a problem with the current request that doesn't return what i hope

SQL

SELECT * FROM Training where _ID NOT IN( select Training_idTraining from Training) and _ID IN(select _ID from TrainingManager where idManager=3)

the request aims to retrieve all the training created by manager with id = 3(this condition explained by second condition after the "where") and that are not assigned to other canceledtraining (explained by the first condition "after the where the table training contains the column "Training_idTraining" which is null if the training is not canceled and filled with the id the future training which will replace the current training if the later was canceled.
when i erase the second condition i get all trainings created by the manager but when i add the first condition to get all the one that aren't assigned to any previous canceled training i don't any record.

Posted 12-Jun-12 1:50am

elidrissi.amine1

Updated 12-Jun-12 6:43am

v2

Add a Solution

3 solutions

Solution 2

Try this:

SQL

SELECT T.*, TM.*
FROM  Training AS T LEFT JOIN TrainingManager AS TM ON T._ID = TM._ID
WHERE NOT T.Training_idTraining IS NULL AND TM.idManager = 3

Posted 12-Jun-12 6:55am

Maciej Los

Comments

Manas Bhardwaj 12-Jun-12 13:18pm

Correct +5

Tim Corey 13-Jun-12 22:09pm

Nice job. +5

VJ Reddy 14-Jun-12 13:27pm

Good answer. 5!

Solution 3

this is the code sql that solve my request:

<pre lang="SQL">SELECT * FROM Training where _ID NOT IN( select Training_idTraining from Training where Training_idTraining is not null) and _ID IN(select _ID from TrainingManager where idManager=3) and Training_idTraining is null

Posted 13-Jun-12 9:13am

elidrissi.amine1

Comments

Tim Corey 13-Jun-12 22:11pm

Couple things. First, you are doing expensive queries (IN and NOT IN) when you have been provided with two working solutions that are less expensive. Second, if you have a working solution, please mark one or more of the solutions as the answer so that future readers know this question is closed. Thank you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Accepted Answer · 2012-06-12T01:57:00

Solution 1

Your requirements are not very clear. But this should be it if it is what I read from your question:

SQL

SELECT
    *
FROM
    Training AS T INNER JOIN TrainingManager AS TM ON
        T._ID = TM._ID
        AND TM.idManager = 3
WHERE
    T.Training_idTraining <> NULL -- this is NULL if the training is cancelled

Posted 12-Jun-12 1:57am

Manas Bhardwaj

Updated 12-Jun-12 7:44am

v3

Comments

Maciej Los 12-Jun-12 12:58pm

Manas, i see a liitle error: INNER JOIN AS TrainingManager TM It should be INNER JOIN TrainingManager AS TM. Besides of that, it's OK. +5!
I have similar solution ;)

Manas Bhardwaj 12-Jun-12 13:19pm

Thx :)

Maciej Los 12-Jun-12 13:23pm

You're welcome ;)

Tim Corey 13-Jun-12 22:09pm

Great job.

VJ Reddy 14-Jun-12 13:26pm

Good answer. 5!