Issue setting flag to Y or N based on outcome

[Programmer99]

I have a query that is returning information from a table like...

Select LastName, FirstName, HasDriversLicenseFlag from Borrower

I need to look in the DriversLicense Table to determine if the borrower has one or not. If they do, display "Y" else Display "N" for the HasDriversLicenseFlag.

Any idea how I can achieve this?

 

[jbenson001]

What is the column type of the HasDriversLicenseFlag column? Is it a bit?

 

[Programmer99]

The HasDriversLicenseFlag is a column, it is just the Column name I want to display for the user.

 

[Programmer99]

I'm thinking I'll have to do some sort of Left Outer Join like...

Select LastName, FirstName, HasDriversLicense
From Borrower b Left Outer Join
DriversLicense dl on b.BorrowerTrID = dl.BorrowerTrID

I just don't know how to display a Y if they have an entry in the DriversLicense table or a N if they do not have an entry in the table. Hope that clarifies.

 

[jbenson001]

You can try this, I have not tested it though:

Select LastName, FirstName, 
HasDriversLicense = 
  CASE 
    WHEN Count(dl.*) > 0
       THEN 'Y'
    WHEN Count(dl.*) = 0
       THEN 'N'
  END
From Borrower b Left Outer Join
  DriversLicense dl on b.BorrowerTrID = dl.BorrowerTrID