CODEWITHSUNDEEP
sql-serverin-clause
Florian
Florian

Reputation: 4738

In clause with 2 fields on 1 table

I have a query :

 SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE logId IN ('1','2','3')

But I want the IN clause test the computerId too like this :

logId = '1' and computerId = '998874',

logId = '2' and computerId = '334211', ...

How to do this ?

Upvotes: 2

Views: 140

Answers (4)

Martin Smith
Martin Smith

Reputation: 453898

IN in SQL Server does not support tuples. You can use EXISTS and a table expression. 2008 syntax below.

SELECT MAX(l.date),
       l.logId,
       l.computerId
FROM   logTable l
WHERE  EXISTS(SELECT *
              FROM   (VALUES ( '1', '998874'),
                              ('2', '334211')) v(logId, computerId)
              WHERE  v.computerId = l.computerId
                     AND v.logId = l.logId)  
GROUP  BY l.logId,
          l.computerId

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

You would have to replace the INstatement with multiple ORstatements

 SELECT  MAX(date)
        ,  logId
        , computerId
 FROM   logTable
 WHERE  (logId = '1' AND computerid = '998874')
        OR (logId = '2' AND computerid = '334211')

Alternatively, if you have many conditions, it might be more readable to create an in-memory table for them and join on these conditions

;WITH Conditions (logID, computerid) AS (
  SELECT '1', '998874'
  UNION ALL SELECT '2', '334211'
)
SELECT  MAX(date)
        ,  logId
        , computerId
FROM   logTable lt
       INNER JOIN Conditions c ON c.logID = lt.LogID 
                                  AND c.computerid = lt.computerID

Upvotes: 2

Garrett Vlieger
Garrett Vlieger

Reputation: 9494

Like this:

SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE (logId = '1' AND computerId = '998874')
    OR (logId = '2' AND computerId = '334211')
    OR (logId = '3' AND computerId = '123456')
 GROUP BY logId, computerId

Upvotes: 0

AllenG
AllenG

Reputation: 8190

I don't think you can do that with an IN clause. I think you need a conditional where. So:

SELECT MAX(date), logId, computerId
FROM logTable
WHERE (logId = 1 AND computerId = '998874') OR
      (logId = 2 AND computerId = '334211') OR
      ...

Upvotes: 1

Related Questions