[AccessD] FW: Query not using criteria

[Bill Benson]

You are joining everything on PID not ChildPID, therefore if there are more
than one ChildPID per those mentioned PIDs, you will of course have
complementary records in which they pass the <> test. You need to also
Outer Join on ChildPID where one side is NULL.

On Thu, May 28, 2015 at 5:14 PM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

>
> I have a query that does not appear to be using the criteria. Below is the
> SQL for the query. Certain ChildPID's should be excluded but they are not
> being excluded. What am I missing? Thanks.
>
> SELECT ConfigMaster.PID, Constants.SDate, ConfigMaster.ChildPID,
> ConfigMaster.WellName
> FROM ([tbl Injectors in All Patterns and Factor] INNER JOIN ConfigMaster
> ON [tbl Injectors in All Patterns and Factor].PID = ConfigMaster.PID) INNER
> JOIN Constants ON ConfigMaster.PID = Constants.PID
> WHERE (((ConfigMaster.ChildPID)<>[tbl Injectors in All Patterns and
> Factor]![ChildPID]))
> GROUP BY ConfigMaster.PID, Constants.SDate, ConfigMaster.ChildPID,
> ConfigMaster.WellName
> HAVING (((ConfigMaster.PID)="PAT 109-1A"));
>
> This is what should be excluded
>
> PID                                         ChildPID
>  Well_Name        Sdate                    Factor1                 Factor2
> PAT 109-1A         42415034110000               109-1
>  8/1/2006              1                              1
> PAT 109-1A         42415046190000               156-1
>  8/1/2006              .25                          .25
> PAT 109-1A         42415307260000               109-7
>  8/1/2006              .333333                 .333333
> PAT 109-1A         42415342080000               109-1A
> 8/1/2006              1                              1
>
> And here are the query results
>
> PID                         SDate
> ChildPID                               WellName
> PAT 109-1A         8/1/2006                              42415017570000
>            111-2
> PAT 109-1A         8/1/2006                              42415034110000
>            109-1
> PAT 109-1A         8/1/2006                              42415034130000
>            109-3
> PAT 109-1A         8/1/2006                              42415046190000
>            156-1
> PAT 109-1A         8/1/2006                              42415307260000
>            109-7
> PAT 109-1A         8/1/2006                              42415341260000
>            111-7
> PAT 109-1A         8/1/2006                              42415341770000
>            156-1A
> PAT 109-1A         8/1/2006                              42415341880000
>            109-3A
> PAT 109-1A         8/1/2006                              42415342080000
>            109-1A
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>