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) A socialist is someone who will give you the shirt off *someone else's* back.
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