Thursday, April 7, 2011

Conditional T-SQL using CASE WHEN

Let's say you need to retrieve data from one table and whether or not each record contains data in the second table, and instead of data from the second table you want to display "yes" or "No". Your best bet is to use CASE WHEN in your SELECT:


SELECT DISTINCT c.CustomerID as [Customer ID], c.CompanyName as [Company Name],
CAST(CASE WHEN o.CustomerID IS NULL THEN 'No' ELSE 'Yes' END as varchar) as [Has Orders]
FROM Customers as c
LEFT JOIN Orders as o ON c.CustomerID=o.CustomerID

The resulting data would look like this:
































Customer ID

Company Name

Has Orders

OCEAN

Océano Atlántico Ltda.

Yes

OLDWO

Old World Delicatessen

Yes

OTTIK

Ottilies Käseladen

Yes

PARIS

Paris spécialités

No

PERIC

Pericles Comidas clásicas

Yes

No comments:

Post a Comment