For SQL Buffs

SQL Apprentice Question
I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). I need a list
of customer numbers that exist in more than one BUSINESS_UNIT_ID.

Celko Answers
>> I have MyTable (ID, BUSINESS_UNIT_ID, CUSTOMER_NUMBER). <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

My guess is that "id" is a totally redundant, non-relational rowing
numbers that newbies who never read anything on RDBMS use. Did you
mean


CREATE TABLE CustomerAssignments
(business_unit INTEGER NOT NULL
REFERENCES OrgChart(business_unit_id)
ON UPDATE CASCADE,
customer_id NTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE ,
PRIMARY KEY (business_unit_id, customer_id));



>> I need a list of customer numbers that exist in more than one BUSINESS_UNIT_ID.<<


SELECT customer_id
FROM CustomerAssignments
GROUP BY customer_id
HAVING COUNT(*) > 1;

Without the key, you would have to use COUNT(DISTINCT business_unit_id)