Select Records With Only One Value In A Column Where Multiple Are Possible

Question

My apologies in advance, this is probably a basic question asked and answered but I don't know how to word the search to find the right results.

I have a table that (among other columns) contains program names for a customer number. I need to identify customers that have only one specific program and no others. A simplified example:

Col1 = Customer_Number, Col2 = Program_Name

Customer 1 has three records because they are enrolled in 2013BA1111, 2013BO1161 and 2013BO1163. Customer 2 has just one record because they are only enrolled in 2013BA1111.

Using Teradata SQL Assistant, if I select WHERE Program_Name = '2013BA1111', both Customer 1 and Customer 2 will be returned since they are both enrolled in program 2013BA1111. I want to select only Customer 2 since they have ONLY 2013BA1111.

Thanks!

Answer 1

In standard (ANSI/ISO) SQL, a derived table is your friend. Here, we join the customer table against a derived table that produces the list of customers having only 1

select *
from customer c
join ( select customer_id
       from customer
       group by customer_id
       having count(program_name) = 1
     ) t on t.customer_id = c.customer_id
where ... -- any further winnowing of the result set occurs here