Casey Crookston Casey Crookston - 4 months ago 10
SQL Question

Select IF EXISTS as a BIT column

I have a query that currently looks like this:

SELECT
d.Region_Name
, d.Dealer_Name
, RIGHT(r.Vin,8) AS VIN
FROM
tblCCDealerCall_Customer c
JOIN tblCCDealerCall d ON c.DealerID = d.DealerID
JOIN tblReservation_TestDrive r ON r.CustomerID = c.CustomerID


Simple enough.But, now I want to add a fourth column called IsVinActive, which is a BIT. I need to check in another table, tblVinDisabledDate, to see if the current VIN exists in that table.

I can use this query to see if a VIN exists in that table:

SELECT CASE WHEN EXISTS
(
SELECT 1 FROM tblVinDisabledDate vd WHERE vd.Vin = '2LMPJ6LP5GBL54709'
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END


But I'm not sure of the best way to integrate that into the first query.

Thanks!

vkp vkp
Answer

You were almost there. Try this.

SELECT
     d.Region_Name
    , d.Dealer_Name
    , RIGHT(r.Vin,8) AS VIN
    , CASE WHEN EXISTS (SELECT 1 FROM tblVinDisabledDate WHERE Vin = r.Vin)
      THEN CAST(1 AS BIT)
      ELSE CAST(0 AS BIT) END
FROM tblCCDealerCall_Customer c
JOIN tblCCDealerCall d ON c.DealerID = d.DealerID
JOIN tblReservation_TestDrive r ON r.CustomerID = c.CustomerID

If you want to avoid a correlated query, join the other table as well.

SELECT
      d.Region_Name
    , d.Dealer_Name
    , RIGHT(r.Vin,8) AS VIN
    , CASE WHEN vd.vin is not null then CAST(1 AS BIT) ELSE CAST(0 AS BIT) END 
FROM tblCCDealerCall_Customer c
JOIN tblCCDealerCall d ON c.DealerID = d.DealerID
JOIN tblReservation_TestDrive r ON r.CustomerID = c.CustomerID
LEFT JOIN tblVinDisabledDate vd ON vd.Vin = r.Vin