Bring a data that not already selected...

Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated.

Its 2 tables "Courses" and "Facilitators"

The Course table contain: Course_Id and Course_Name

The Facilitators table contain: Facili_Id and Facili_Name

The two tables have (many to many) relation between them.

So I split them into a 3^rd table called "Trans" which contain the PK from each Table

That will be : "Trans_Course_Id" and "Trans_Facili_Id"

I put some data on each table… at least 3 records and related some of them in the "Trans" table

Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course?

Like if I have the data in the "Courses" table
id: 1 and Name: VB

Id:2 and Name: C#

And in "Facilitators" table:

Id:1 and Name: Adam

Id:2 And Name: George

Id:3 and Name: Sam

Now in the relation table "Trans"

Course_Id:1 and Facili_Id:1

Course_Id:2 and Facili_Id:1

Course_Id:2 and Facili_Id:3

Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name"

For Course_id "For example" and should not be selected by the same course…

That would be:

Id:2 And Name: George

Id:3 and Name: Sam

And the same for eash time I pass the course_id for the command

Thank you.

I'm lost. I see your example data, but I don't see how you get George and Sam using any thing that remotely resembles what you've explained. Explain again, and I'm sure I can get you the T-SQL.

|||

Ok… its just only wanted to get all the data that not already selected by a cretin course.

lets say that the course id 1 has select a facilitator id 1 now I want the sql command that brings me all the facilitator id's except the facilitator id 1 for the course id 1 .

ill explain to you what I do exactly

I have a form that brings all a certain course information and a data grid that show the facilitator id's and names for that course.

Now when the admin want to add another facilitator for this course he press a button that will create a child window contain a LOV "list of value" as a data grid contain all the facilitator id's and names that not already selected by that certain course in the parent window "data grid"….

??

|||

I think I see...

So if the statement:

SELECT facil_id from trans WHEREcourse_id=@.course_id

returns to you the facilitators, then

SELECT *

FROM Facilitators

WHERE facil_id NOT IN (SELECT facil_id FROM trans WHEREcourse_id=@.course_id)

will return you all the facilitators that aren't part of the course id you passed in... Does that answer your question?

|||

Will. You know I have to do it in one select statement

So what I did is :

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id = [transaction_ facilitators].trans_Facilitator_id

WHERE (facilitators.facilitator_id NOT IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM transaction_ facilitators

WHERE [transaction_ facilitators].trans_Course_id = @.p1))

ORDER BY facilitators.facilitator_id

But that didn't work :( …

I know the answer is some where here but I can't catch it.

|||

OK this one work great:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = 2)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

But when I want to pass a parameters to "[transaction_ facilitators].trans_Course_id = @.p1"

This gives me an error:

Incorrect syntax near the keyword "From"

Any suggestion ??

|||

Maybe you misunderstood. The statement:

SELECT *

FROM Facilitators

WHERE facil_id NOT IN (SELECT facil_id FROM trans WHEREcourse_id=@.course_id)

should be all you need.

|||

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = 2)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Every thing goes right until I change this:

WHERE [transaction_ facilitators].trans_Course_id = 2

To

WHERE [transaction_ facilitators].trans_Course_id = @.course_id

It gives me this error:

Incorrect syntax near the keyword "From"

still gives me the same error

|||

Unbalanced parenthesis.

You are also using group by with no aggregates -- Which does the same thing as distinct.

SELECT DISTINCTfacilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = @.course_id))

ORDER BY facilitators.facilitator_id

|||

:( That's unbelievable… its not working what is this… look I try this:

SELECT DISTINCT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = @.course_id)))

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT DISTINCT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = @.course_id)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = ?)))

GROUP BY facilitators.facilitator_id, facilitators.facilitator_name

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

And I try this:

SELECT facilitators.facilitator_id, facilitators.facilitator_name

FROM facilitators INNER JOIN

[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id

WHERE (NOT (facilitators.facilitator_id IN

(SELECT [transaction_ facilitators].trans_Facilitator_id

FROM [transaction_ facilitators]

WHERE [transaction_ facilitators].trans_Course_id = ?)))

ORDER BY facilitators.facilitator_id

Not Working the same error: "Incorrect syntax near the keyword "From"

"

Nothing work what is this… is it a curse ?

I don't have more time :'(… HELP

|||Good, now that you've tried all those, and they didn't work, try what I posted two messages up. Each of your "tries" has unbalanced parenthesis, which is what is causing your error message.