Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the same title, subtitle, and sub-subtitle but different ID's. For the past several years, these articles were not related in the DB, however a redesign on the website now makes creating these relationships necessary. The only way I have to join them together is by the title, subtitle, and sub-subtitle.

I can create a pair of temporary tables and join them, but I get exactly double the needed records.

/*create & populate a temporary table to staore the lookup data*/
drop TEMPORARY table if exists Product1;
CREATE TEMPORARY TABLE Product1 (
ProductID  int NOT NULL ,
Title  varchar(255) NULL ,
SubTitle  varchar(255) NULL ,
SubSubTitle  varchar(255) NULL ,
PRIMARY KEY (ProductID),
INDEX idx_titles (Title, SubTitle, SubSubTitle) 
);
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (1, "Big Ideas", "Where they come from", "Stackexchange");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (2, "Big Ideas", "Where they come from", "Stackexchange");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (3, "Big Ideas", "Where they come from", "Interwebs");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (4, "Big Ideas", "Where they come from", "Interwebs");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (5, "Little Ideas", "Why Bother?", "");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (6, "No Idea", "Where I am right now", "");
INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (7, "No Idea", "Where I am right now", "");

/*create * populate a duplicate table*/
drop TEMPORARY table if exists Product2;
CREATE TEMPORARY TABLE Product2 LIKE Product1;
INSERT Product2 SELECT * FROM Product1;

/*build join*/
drop TEMPORARY table if exists VariationJoin;
CREATE TEMPORARY TABLE VariationJoin (
ProductID1  int NOT NULL ,
ProductID2  int NOT NULL ,
Title  varchar(255) NULL ,
SubTitle  varchar(255) NULL ,
SubSubTitle  varchar(255) NULL ,
PRIMARY KEY (ProductID1, ProductID2)
);
INSERT INTO VariationJoin
SELECT p1.ProductID as ProductID1, p2.ProductID as ProductID2, p1.Title,p1.SubTitle,p1.SubSubTitle
FROM Product1 p1
INNER JOIN Product2 p2 ON p1.Title = p2.Title AND p1.SubTitle = p2.SubTitle AND p1.SubSubTitle = p2.SubSubTitle AND p1.ProductID <> p2.ProductID;

The resulting table is a Cartesian join.

/*get the results*/
SELECT * FROM VariationJoin ORDER BY Title ASC, SubTitle ASC, SubSubTitle ASC;
ProductID1   | ProductID2   | Title      | SubTitle                | SubSubTitle
3            | 4            | Big Ideas  | Where they come from    | Interwebs
4            | 3            | Big Ideas  | Where they come from    | Interwebs
1            | 2            | Big Ideas  | Where they come from    | Stackexchange
2            | 1            | Big Ideas  | Where they come from    | Stackexchange
6            | 7            | No Idea    | Where I am right now    | 
7            | 6            | No Idea    | Where I am right now    | 

If I add another record that has the same title, subtitle, and sub-subtitle INSERT INTO Product1 (ProductID, Title, SubTitle, SubSubTitle) VALUES (8, "No Idea", "Where I am right now", ""); The Cartesian join table now has 10 records when I only want 4.

ProductID1   | ProductID2   | Title      | SubTitle                | SubSubTitle
3            | 4            | Big Ideas  | Where they come from    | Interwebs
4            | 3            | Big Ideas  | Where they come from    | Interwebs
1            | 2            | Big Ideas  | Where they come from    | Stackexchange
2            | 1            | Big Ideas  | Where they come from    | Stackexchange
6            | 7            | No Idea    | Where I am right now    | 
6            | 8            | No Idea    | Where I am right now    | 
7            | 6            | No Idea    | Where I am right now    | 
7            | 8            | No Idea    | Where I am right now    | 
8            | 6            | No Idea    | Where I am right now    | 
8            | 7            | No Idea    | Where I am right now    | 

The order of the ProductIDs doesn't matter. How would I limit the results to only those records I need? Is there a better way to do this?

EDIT The desired result set would be:

ProductID1   | ProductID2   | Title      | SubTitle                | SubSubTitle
3            | 4            | Big Ideas  | Where they come from    | Interwebs
1            | 2            | Big Ideas  | Where they come from    | Stackexchange
6            | 7            | No Idea    | Where I am right now    | 
6            | 8            | No Idea    | Where I am right now    | 
7            | 8            | No Idea    | Where I am right now    | 
share|improve this question
    
You got me confused. Which is the base table(s)? And what is the wanted output? –  ypercube Oct 17 '13 at 16:31
    
Do you just need SELECT MIN(ProductID), Title, SubTitle, SubSubTitle FROM Product GROUP BY Title, SubTitle, SubSubTitle ; ? –  ypercube Oct 17 '13 at 16:33
    
The base table is a list of articles. I created a temporary table to reduce the stress on the live table. If three records are related, (e.g. 6,7, & 8), I want 6-7, 6-8, and 7-8 join in the table. If I SELECT MIN(ProductID) ...; then I only end up with a 6-7 and 6-8 join. –  Justin Oct 17 '13 at 20:30
    
Can you edit the question and add the result you want? –  ypercube Oct 17 '13 at 20:35

1 Answer 1

up vote 1 down vote accepted

It seems you want a triangular self-join. Just change the <> to <:

AND p1.ProductID < p2.ProductID

You also do not need the copy table Product2. You can join the table to itself. See SQL-Fiddle:

INSERT INTO VariationJoin
SELECT 
    p1.ProductID as ProductID1,
    p2.ProductID as ProductID2, 
    p1.Title, p1.SubTitle, p1.SubSubTitle
FROM Product1 p1                              -- same table
  INNER JOIN Product1 p2                      -- different alias
    ON  p1.Title = p2.Title 
    AND p1.SubTitle = p2.SubTitle 
    AND p1.SubSubTitle = p2.SubSubTitle 
    AND p1.ProductID < p2.ProductID ;
share|improve this answer
    
That was it! Thanks for the help. –  Justin Oct 18 '13 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.