Different primary keys treated as one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Different primary keys treated as one

Author

Topic

hpet
Starting Member

3 Posts

Posted - 2010-12-09 : 02:21:36

Hello,

I have quite a specific question where I need to workaround some fundamental "by design" issue.

Simplified case description:
1. customer database - customer table (PK: customer_id)
2. due to "by design" issue user can insert a new customer that is logicaly a duplicate to already existing customer (different PK, but logicaly the same, just user didn't bother to look it up)
3. at some later point this is discovered
4. one PK is now programaticaly merged into the selected one
5. BUT again, due to some silly by design issue, for example purchased items are NOT transfered - they remain with the original address, which gets status of "closed".

Now I need to query such DB and I must be able to get all purchased items for all customers - including those purchased on possibly linked and "closed" customer as well.

This "chain" can be, unfortunately, deeper:
PK: 1, customer1 -> closed,merged to 2
PK: 2, customer2
PK: 3, customer3 -> closed,merged to 2

A table which keeps this reference exists: customer_id, merged_customer_id

which PK is "merged" with which one depends on the user's selection.

I have no idea where and how to start putting together query that will somehow "treat" different but linked PKs in chain "as one".

Thank you for all help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 03:34:47

The easy way would be to update the cust_id in the orders table. Then you can do a "normal" select.

No, you're never too old to Yak'n'Roll if you're too young to die.

hpet
Starting Member

3 Posts

Posted - 2010-12-09 : 03:53:40

quote:
Originally posted by webfred

The easy way would be to update the cust_id in the orders table. Then you can do a "normal" select.

No, you're never too old to Yak'n'Roll if you're too young to die.

I unfortunately can't. Not my app. I need to find SQL server side solution.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 04:05:37

Is there a column STATUS in the customer table?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 04:30:00

[code]
SELECT Col1, Col2, ...
FROM PurchasedItems AS PI
JOIN
(
SELECT MC.customer_id
FROM MergedCustomers AS MC
WHERE merged_customer_id = 1234
UNION ALL
SELECT 1234
) AS MC
ON MC.customer_id = PI.customer_id
[/code]
Substitute 1234 for the Customer ID you want (or a parameter, or something else ...)

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 04:31:05

"The easy way would be to update the cust_id in the orders table."

That would lose the "history" of the fact that people had ordered using a now-merged account. (May not be important, but IME we keep that sort of hitorical data because it tends to BECOME important later!!)

hpet
Starting Member

3 Posts

Posted - 2010-12-09 : 04:54:30

quote:
Originally posted by Kristen

"The easy way would be to update the cust_id in the orders table."

That would lose the "history" of the fact that people had ordered using a now-merged account. (May not be important, but IME we keep that sort of hitorical data because it tends to BECOME important later!!)

Correct, although I think there are better ways to solve this. I belive that this solution is like this due to some old legacy software and they just continue using this structure.

I will see where this query leads me.
Thanks so far.

@webfred: Yes, I have all available status information: closed, merged, active, etc.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-10 : 16:05:40

Sounds like an equivalence class problem. Let's change the Merged Customers table a little bit:

CREATE TABLE Redundant_Customer_IDs
(primary_customer_id CHAR(15) NOT NULL,
merged_customer_id CHAR(15) NOT NULL UNIQUE,
CHECK (customer_id <= merged_customer_id ));

Seed the table with some customer_id column to each equivalence class a name. That is, everyone has a (primary_customer_id = merged_customer_id) row to start.

When you get a (new_customer_id = merged_customer_id) situation, you add a (primary_customer_id, new_customer_id) row to the table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-11 : 14:43:07

quote:
Originally posted by hpet

This "chain" can be, unfortunately, deeper:
PK: 1, customer1 -> closed,merged to 2
PK: 2, customer2
PK: 3, customer3 -> closed,merged to 2

A table which keeps this reference exists: customer_id, merged_customer_id

which PK is "merged" with which one depends on the user's selection.

Can the merging/closing be daisy-chained, as in the example below:

PK: 1, customer1 -> closed,merged to 2
PK: 2, customer2 -> closed,merged to 3
PK: 3, customer3

If that can happen, you would need a recursive query (a recursive CTE for example). But, I am probably imagining a solution for a problem that may not exist - so I will shut up :--)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources