For SQL Buffs

[maxvalery]

Hi,

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.

Thanks!

[markc600]

SELECT CUSTOMER_NUMBER
FROM MyTable
GROUP BY CUSTOMER_NUMBER
HAVING COUNT(DISTINCT BUSINESS_UNIT_ID)>1

[maxvalery]

Awesomeness.

Didn't know you could throw that DISTINCT in there like that.

[--CELKO--]

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