Nested query?

[jararaca]

Hi,

I have two tables in a one-to-many, parent-child relationship:

Demographic 1 - Many Credentialing

There is s foreign key called DemoId in Credentialing that points to the primary key, Id, in Demographic.

Each row of the Demographic table represents a person, and their credentialing history is kept in the Credentialing table. I need to write a query that will list all persons in the Demographic table, along with a yes/no field that will be yes if the person has credentials (at least one row in the Credentialing table), and no if not:

LastName FirstName Credentialed
Smith John Yes
Brown Mary No
etc.

Can anyone please tell me how this query would be written?

Thank you!

 

[swampBoogie]

select LastName, FirstName,
  case when min(credentials.fk) is null 
then 'No' else 'Yes' end as credentaials
from demographics left join credentials
on demographics.pk = credentials.fk
group by lastName,FirstName

 

[jararaca]

Works beautifully! Thank you very much!