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 3rd 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.
