Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

teradata

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!

like image 499
user3298276 Avatar asked Feb 11 '14 17:02

user3298276


People also ask

How do I SELECT multiple values from the same column in SQL?

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);

How do I SELECT multiple columns based on condition in SQL?

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.


Video Answer


1 Answers

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
like image 163
Nicholas Carey Avatar answered Nov 15 '22 18:11

Nicholas Carey