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!
Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);
When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With