Entry
Database: SQL: Table: Operation: Join: Product: Cartesian: How to possibly program a join operation?
Mar 23rd, 2005 10:36
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 22 March 2005 - 06:53 pm ----------------------
Database: SQL: Table: Operation: Join: Product: Cartesian: How to
possibly program a join operation?
---
Creating a join of tables can basically seen as a cartesian product (or
thus some kind of a 'multiplication' operation), that is, you check
each row of the first table against each row of a second table.
---
So you could possibly program a join as a double for next loop:
---
--- cut here: begin --------------------------------------------------
FOR first to last row of table1
FOR first to last row of table2
// do something
ENDFOR
ENDFOR
--- cut here: end ----------------------------------------------------
---
---
more detailed:
---
--- cut here: begin --------------------------------------------------
FOR first to last row of table1
-get primary key of that row
FOR first to last row of table2
-get foreign key of that row
-compare that primary key to that foreign key
-if they are equal, then do something with that row
ENDFOR
ENDFOR
--- cut here: end ----------------------------------------------------
---
---
--- cut here: begin --------------------------------------------------
+---------------------+ +---------------------+
|row 1 primary key |----->-----+--->|row 1 foreign key |
+---------------------+ | +---------------------+
| | +--->|row 2 foreign key |
+---------------------+ | +---------------------+
| | +--->|row 3 foreign key |
+---------------------+ | +---------------------+
| | +--->|row 4 foreign key |
+---------------------+ | +---------------------+
+--->+row 5 foreign key |
| +---------------------+
+--->|row 6 foreign key |
| +---------------------+
|
| ...
|
| +---------------------+
+--->|row 6 foreign key |
+---------------------+
--- cut here: end ----------------------------------------------------
---
---
When doing an inner join, you are basically doing that double loop row
comparison:
If you have 2 tables:
--- cut here: begin --------------------------------------------------
SELECT *
FROM table1, table2
WHERE table1.primarykey = table2.foreignkey
--- cut here: end ----------------------------------------------------
---
---
If you have 3 tables:
--- cut here: begin --------------------------------------------------
SELECT *
FROM table1, table2, table3
WHERE table1.primarykey = table2.foreignkey
AND
table2.foreignkey = table3.foreignkey
--- cut here: end ----------------------------------------------------
---
---
If you have 4 tables:
--- cut here: begin --------------------------------------------------
SELECT *
FROM table1, table2, table3, table4
WHERE table1.primarykey = table2.foreignkey
AND
table2.foreignkey = table3.foreignkey
AND
table3.foreignkey = table4.foreignkey
--- cut here: end ----------------------------------------------------
...
If you have N tables:
--- cut here: begin --------------------------------------------------
SELECT *
FROM table1, table2, table3, table4, ..., tableN
WHERE table1.primarykey = table2.foreignkey
AND
table2.foreignkey = table3.foreignkey
AND
table3.foreignkey = table4.foreignkey
AND
...
tableN-1.foreignkey=tableN.foreignkey
--- cut here: end ----------------------------------------------------
---
---
What is the relation between total amount of tables and total amount of
loops?
In some cases you might have:
-If you have 2 tables, you have 2 nested 'for next' loops to test.
-If you have 3 tables, you have 3 nested 'for next' loops to test.
-If you have 4 tables, you have 4 nested 'for next' loops to test.
...
-If you have N tables, you have N nested 'for next' loops to test.
---
---
Internet: see also:
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
