Mysql – How to prevent Cartesian joins when value can be in either field

join;MySQLmysql-5.5

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    |

Best Answer

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 ;

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

MySQL – Get Column from Multiple Tables

Related Question