gproulx
Posts: 68
|
| Posted: 12/14/2009, 9:01 AM |
|
Hi,
i have a small problem on how i can formulate a query.
I have 2 tables:
Table1 (personal infos) like Part_ID, FirstName, LastName, gender, etc.
Table2 (assignement infos) with a left join to Part_ID from Table 1, tableNo, SeatNo, Assignement_ID, etc.
So for an assignement ID, i assign peoples from the first table with the Part_ID.
I have a grid where i can delete assignement done in the Table2.
What i need to do is to have a list of peoples who are not assigned for a specific assignement. It is difficult to me to explain (english is not my primary language and it's difficult to explain anyway...lol)
So, the equation i need is something like:
(SELECT * from Table 1) - (SELECT * FROM Table1 LEFT JOIN Table2 ON
Table1.Part_ID = Table2.Part_ID) = Only display non-assigned people
I hope someone can help me
thanks
|
 |
 |
ckroon
Posts: 869
|
| Posted: 12/14/2009, 10:50 AM |
|
Where table2.Part_ID = Null
_________________
Walter Kempees...you are dearly missed. |
|
|
|
datadoit
|
| Posted: 12/14/2009, 2:57 PM |
|
Look at using a sub-select with NOT EXISTS. Ex:
SELECT * FROM Table 1 WHERE NOT EXISTS (SELECT * FROM Table 1 ...)
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-e...subqueries.html
|
|
|
|
gproulx
Posts: 68
|
| Posted: 12/15/2009, 7:16 AM |
|
Thanks for answering.
I use NOT EXIST like you mention datadoit and it works. In the visual query builder, i put this code in the 'WHERE' (Condition Type=Expression)
Expression: NOT EXISTS (SELECT * FROM Table2 WHERE Table2.Part_ID = Table1.Part_ID )
It works but it is the right place to put that? Also, i would like to add another where clause to this expression, something like : AND Table2.ID_Assignement = AssignID (Where 'AssignID' is a parameter i have in a search form over my grid)
I try several things with no success. It's just how i have to write this parameter if this one is send in the url? (ex.: Table2.ID_Assignement = "AssignID", Table2.ID_Assignement = {AssignID}, Table2.ID_Assignement = (AssignID), etc.)
Thanks in advance!
|
|
|
|
|
