CODEWITHSUNDEEP
sqlt-sqlselectjoin
Casey Crookston
Casey Crookston

Reputation: 13955

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!

Upvotes: 0

Views: 2681

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

;With cte
as
(
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
)
select * ,case when b.vin is null then 0 else 1 end as 'somecol 
from cte c
outer apply
(select 1  as vin from tblVinDisabledDate vd where vd.vin=c.vin) b

Upvotes: 1

Related Questions