how to compare 3 primary Key ? 2

[kkgusta]

Hi
If I have 2 tables

Table one has:
-ProjectID
-BuildID
-ScriptID
-Script details

Table two has:
-ProjectID
-BuildID
-ScriptID
-Result

ProjectID,BuildID and ScriptID are the primary keys for these table. If I want to find out the scripts that not exists in table two what kind of where statement I need to use? I have tried using 3 where statement to compare the 3 primary id but the result is always wrong.

 

[r937]

select T1.ProjectID
     , T1.BuildID
     , T1.ScriptID
  from tableone as T1
left outer
  join tabletwo as T2
    on T2.ProjectID = T1.ProjectID 
   and T2.BuildID   = T1.BuildID   
   and T2.ScriptID  = T1.ScriptID  
 where T2.ProjectID is null

if that gives you an error, i think stupid msaccess requires parentheses

select T1.ProjectID
     , T1.BuildID
     , T1.ScriptID
  from tableone as T1
left outer
  join tabletwo as T2
    on [red]([/red]
       T2.ProjectID = T1.ProjectID 
   and T2.BuildID   = T1.BuildID   
   and T2.ScriptID  = T1.ScriptID  
       [red])[/red] 
 where T2.ProjectID is null

r937.com | rudy.ca

 

[randy700]

MS Access also has a built in query wizard designed for just this situation. Go to queries, new, and select Find Unmatched Query Wizard.


Randy

 

[kkgusta]

Yup got it work.
thank for for all your reply