having problems with <> working

[DLB]

I have a Computer table and a Service table.  They are related on field pk_ComputerId and fk_ComputerId respectively.  I am trying to find all the computers that do not have a service call.  I have been able to find the computers that do no problem as follows:

 

ExecuteSQL ( "SELECT pk_ComputerId FROM Computer WHERE pk_ComputerId = ANY(SELECT fk_ComputerId FROM Service)" ; "" ; "" )

-or-

ExecuteSQL ( "SELECT pk_ComputerId FROM Computer c JOIN Service s ON c.pk_ComputerId = s.fk_ComputerId)" ; "" ; "" )

 

I must be missing something in my logic because I would think you could replace the = with a <> in either of the statements above and get a list of computers that do not have service calls.  But the result I get is all of the records in the Computer table regardless if they have an entry in the Service table or not.

 

Can someone please help me adjust my logic on this!

[eos]

You want all computerIDs from Computers that are not represented in the Services table.

 

The easiest way to express that statement in SQL is probably:

ExecuteSQL ( "
  SELECT pk_ComputerId 
  FROM Computer 
  WHERE pk_ComputerId NOT IN
    ( SELECT DISTINCT ( fk_ComputerId ) FROM Service )
  " ; "" ; "" 
)

[Wim Decorte]

make sure you take empty values into account: 

 

http://www.soliantconsulting.com/blog/2013/11/executesql-and-empty-values

[LaRetta]

This is new to me as well!  Thank you, Wim! 

[DLB]

Thank you eos!  That worked great.  I had tried NOT but was not familiar with NOT IN. 

 

 

WIM thanks for the heads up, but in this case I am dealing with keys so if I have a blank I have other problems.

[Kris M]

DLB.. I would guess that the "ANY" part of this expression is NOT supported by ExecuteSQL  ...ExecuteSQL ( "SELECT pk_ComputerId FROM Computer WHERE pk_ComputerId = ANY(SELECT fk_ComputerId FROM Service)" ; "" ; "" )