CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Greg Gum
Greg Gum

Reputation: 37919

How to use all in where clause

I want to write this query using all, but it is not valid SQL:

SELECT *
      FROM OrderCustPlankLoad ocl
      WHERE ocl.PlankLoadStatusId = 2 and all 
      (Select PlankLoadStatusId from OrderAccountPlankLoad oal where oal.PlankLoadStatusId = 2
      and ocl.PlankClientId = oal.PlankClientId
      )

What I want to ensure is that all related records in the related table have a status of 2.

But I don't think I am writing this correctly - SSMS does not like the "All" as well as "ocl.PlankClientId" in the query.

What I am doing is ensuring that all the records are valid before I start processing them. I don't want to process the rows in ocl if there are related rows in oal that are not valid.

How do I write this correctly?

Upvotes: 0

Views: 156

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

select *
      FROM OrderCustPlankLoad ocl
      WHERE ocl.PlankLoadStatusId = 2 and not exists
      (Select 1 from OrderAccountPlankLoad oal where oal.PlankLoadStatusId <>2
      and ocl.PlankClientId = oal.PlankClientId
      )

Upvotes: 1

Greg Gum
Greg Gum

Reputation: 37919

I came up with this which seems to be working:

SELECT *
FROM OrderCustPlankLoad ocl
WHERE ocl.PlankLoadStatusId = 2
AND NOT EXISTS (
    SELECT 1
    FROM OrderAccountPlankLoad oal
    WHERE oal.PlankLoadStatusId = 5
    AND ocl.PlankClientId = oal.PlankClientId
)

Upvotes: 0

Nike
Nike

Reputation: 1307

I think this is what you mean:

  SELECT *
  FROM OrderCustPlankLoad ocl
  WHERE ocl.PlankLoadStatusId = 2
      AND ocl.PlankClientId IN 
            (SELECT oal.PlankClientId 
             FROM OrderAccountPlankLoad oal
             WHERE oal.PlankLoadStatusId = 2
             )

Upvotes: 0

Related Questions