August 26, 2016 at 1:28 pm
I have a table Consumer and Consumer Account. The consumer can be primary consumer or secondary consumer. Multiple consumers and have the same account.
I need to link the consumer together.
CREATE TABLE Consumer (Consumerid INT,
ConsumerAccountID INT,
ConsumerType CHAR(1))
INSERT INTO Consumer SELECT 1, 1, 'P'
INSERT INTO Consumer SELECT 2, 1, 'S'
INSERT INTO Consumer SELECT 3, 1, 'S'
CREATE TABLE LinkConsumer (FromConsumerID INT,
ToConsumerID INT)
INSERT INTO LinkConsumer (FromConsumerID, ToConsumerID)
SELECT p.ConsumeriD, s.ConsumerID
FROM (
SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'P') p
INNER JOIN (SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'S') s ON p.ConsumerAccountID = s.ConsumerAccountID
The value is (1, 2) and (1, 3)
Then I want to link the secondary consumer
INSERT INTO LinkConsumer (FromConsumerID, ToConsumerID)
SELECT p.ConsumeriD, s.ConsumerID
FROM (
SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'S') p
INNER JOIN (SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'S') s ON p.ConsumerAccountID = s.ConsumerAccountID
However the second query generated the following result in the LinkConsumer table (2, 3) and (3, 2)
How do eliminate the second set of value (3, 2)?
Thanks
August 26, 2016 at 2:18 pm
Add an extra join condition so you only get a single occurrence:
...
INNER JOIN (SELECT ConsumerID, ConsumerAccountID
FROM Consumer
WHERE ConsumerType = 'S') s ON p.ConsumerAccountID = s.ConsumerAccountID
AND p.ConsumerID < s.ConsumerID
Edit: Moved AND condition to separate line to make it easier to see in code window.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 26, 2016 at 2:52 pm
Thanks it worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply