SQL Cartesian product joining table to itself and inserting into existing table
- by Emma
I am working in phpMyadmin using SQL.
I want to take the primary key (EntryID) from TableA and create a cartesian product (if I am using the term correctly) in TableB (empty table already created) for all entries which share the same value for FieldB in TableA, except where TableA.EntryID equals TableA.EntryID
So, for example, if the values in TableA were:
TableA.EntryID TableA.FieldB
1 23
2 23
3 23
4 25
5 25
6 25
The result in TableB would be:
Primary key EntryID1 EntryID2 FieldD (Default or manually entered)
1 1 2 Default value
2 1 3 Default value
3 2 1 Default value
4 2 3 Default value
5 3 1 Default value
6 3 2 Default value
7 4 5 Default value
8 4 6 Default value
9 5 4 Default value
10 5 6 Default value
11 6 4 Default value
12 6 5 Default value
I am used to working in Access and this is the first query I have attempted in SQL.
I started trying to work out the query and got this far. I know it's not right yet, as I’m still trying to get used to the syntax and pieced this together from various articles I found online. In particular, I wasn’t sure where the INSERT INTO text went (to create what would be an Append Query in Access).
SELECT EntryID
FROM TableA.EntryID
TableA.EntryID
WHERE TableA.FieldB=TableA.FieldB
TableA.EntryID<>TableA.EntryID
INSERT INTO TableB.EntryID1
TableB.EntryID2
After I've got that query right, I need to do a TRIGGER query (I think), so if an entry changes it's value in TableA.FieldB (changing it’s membership of that grouping to another grouping), the cartesian product will be re-run on THAT entry, unless TableB.FieldD = valueA or valueB (manually entered values).
I have been using the Designer Tab. Does there have to be a relationship link between TableA and TableB. If so, would it be two links from the EntryID Primary Key in TableA, one to each EntryID in TableB? I assume this would not work because they are numbered EntryID1 and EntryID2 and the name needs to be the same to set up a relationship?
If you can offer any suggestions, I would be very grateful.
Research:
http://www.fluffycat.com/SQL/Cartesian-Joins/
Cartesian Join example two
Q: You said you can have a Cartesian join by joining a table to itself. Show that!
Select *
From Film_Table T1,
Film_Table T2;
