From: bkausbk@web.de   
      
   Hello Mr. Sommarskog,   
      
   thanks for your reply.   
      
   > Note, by the way, that the query can return more than one row for   
   > the same (RouterId, DeviceID), for instance if you have one tuple with   
   > Metric = 95 and one with Metric = 105.   
      
   Yes, but this wouldn't be a problem in my case, but you are right.   
      
   >   
   > And important property of a derived table is that it is blind for   
   > tables outside it, why you cannot refer to DeviceTable it. Keep in   
   > mind, that logically the derived table is computed first.   
      
   OK that explains everything :)   
      
   > Looking at your query it seems to me that you need the DeviceTable   
   > in two places:   
   >   
   >     SELECT LT1.RouterId, LT1.DeviceId, LT1.Metric   
   > [...]   
      
   OK, but now the Question is (if this would work in Compact Edition),   
   would it make sense to make such a complex query? May be it is better to   
   put some logic outside of the query into the main application?   
      
   My real database is even more complex, but I omitted to post tons of   
   lines of code :)   
      
   What, if there are not only MAC Address parameters, but also Metric   
   parameters. In my real table there is no metric. Both are provided   
   parameters:   
      
   @p1             @p2   
   MAC             METRIC   
   0x1234567809ab  100   
   0x234567809abc  90   
   ...             ...   
      
   I tried it with that temporary table, but as you said this is not possible:   
      
   SELECT B.DeviceId, A.MacAddress, A.Metric FROM   
   (   
   (   
      SELECT NULL AS DeviceId, 0x1[...]b AS MacAddress, 100 AS Metric   
        UNION   
      SELECT NULL AS DeviceId, 0x2[...]c AS MacAddress, 90 AS Metric   
        UNION   
      ...   
   ) AS A LEFT OUTER JOIN DeviceTable B ON (A.MacAddress = B.MacAddress)   
   ) AS C   
      
   OK, but I think, it is better to call that query for each of the MAC   
   addresses on my list instead to create a real huge query (if this would   
   be possible).   
      
   Regards,   
   Benjamin Kalytta   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)