|
|
Posted by --CELKO-- on 09/15/06 19:40
>> 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)
Navigation:
[Reply to this message]
|