Mysql self join

[amit_patel]

Hello all,

I read something about self join but i don't know in which situation it is useful. So Plz give me some example in which self join will be useful.

Thanks and regards,
----Amit Patel

[Weedpacket]

A table of employees. Employees have superiors, but superiors are also employees so they're in the employees table as well.

[Roger_Ramjet]

Another example.

Table digits contains 1 column and 10 rows with 0-9

SELECT CInt([digits_2].[digit] & [digits_1].[digit] & [digits].[digit]) AS number_sequence
FROM digits, digits AS digits_1, digits AS digits_2;

This is a cartesian self-join that produces a number sequence from 0 - 999. The original output would be a string sequence from 000 - 999 which is cast to an integer to drop the leading '0's. The point of this example is to demonstrate the combinatorial possibilities of self-joins.

PS the syntax is JET SQL for Access so CInt() needs to be changes to the cast function of the specific platform.