CODEWITHSUNDEEP
sqlsql-serverexists
Amit Joshi
Amit Joshi

Reputation: 97

How to use exists for a complete list of a product?

This is the query to find out if a particular car has W1, W2, WA, WH conditions. How can I modify my query so that I can get a list of all the cars as yes or no for these conditions?

NOTE: Here, I have put v.[carnumber] = 't8302' but I need a complete list.

SELECT 
    CASE 
    WHEN EXISTS (
        SELECT co.[alias]    
        FROM  [MTI_TAXI].[vehicle] v
        LEFT JOIN [MTI_SYSTEM].[Conditions] co with (nolock) on v.DispatchSystemID = co.DispatchSystemID and (v.Conditions & co.conditionvalue > 0)
        WHERE co.[alias] in ('W1', 'W2', 'WA', 'WH') and v.[DispatchSystemID] = 6 and v.[CarNumber] = 't8302')    
    THEN cast ('Yes' as varchar)   
    ELSE cast ('No' as varchar)    
    END AS [WATS]

OUTPUT - ( WATS - No )

But, here are all the cars but I am getting yes to WATS condition which is incorrect

enter image description here

Upvotes: 0

Views: 210

Answers (2)

Bohemian
Bohemian

Reputation: 425238

Use count(*) to assert that there was exactly 1 row found by adding:

group by co.[alias]
having count(*) = 1

So the whole query becomes:

SELECT 
  CASE 
    WHEN EXISTS (
      SELECT co.[alias]
      FROM [MTI_TAXI].[vehicle] v
      LEFT JOIN [MTI_SYSTEM].[Conditions] co with (nolock)
        on v.DispatchSystemID = co.DispatchSystemID
        and (v.Conditions & co.conditionvalue > 0)
      WHERE co.[alias] in ('W1', 'W2', 'WA', 'WH')
      and v.[DispatchSystemID] = 6
      and v.[CarNumber] = 't8302'
      group by co.[alias]
      having count(*) = 1
    ) THEN cast ('Yes' as varchar)
    ELSE cast ('No' as varchar)
  end AS [WATS]

Upvotes: 0

trenton-ftw
trenton-ftw

Reputation: 972

Simply utilizing your provided filters and moving the EXISTS to be used in an OUTER APPLY statement:

SELECT 
    CASE 
        WHEN [find_wats].[Found] = 1 
            THEN 'Yes' 
        ELSE 'No' 
    END AS [WATS]
FROM 
    [MTI_TAXI].[vehicle] AS v
    OUTER APPLY (SELECT TOP (1) 
                    1 AS [Found]
                FROM 
                    [MTI_SYSTEM].[Conditions] AS co
                WHERE 
                    v.DispatchSystemID = co.DispatchSystemID 
                    AND 
                    (v.Conditions & co.conditionvalue > 0)
                    AND 
                    co.[alias] IN ('W1', 'W2', 'WA', 'WH') 
                    AND 
                    v.[DispatchSystemID] = 6) AS [find_wats];

Using this set up, you can then use [find_wats].[Found] = 1 to determine that your record within the table [MTI_TAXI].[vehicle] found a match in [MTI_TAXI].[Conditions] (using your provided criteria) while still maintaining a single record in your final result set for each record originally in the table [MTI_TAXI].[vehicle].

Upvotes: 1

Related Questions