I have a table that I wish to join to itself, but I get all sorts of problems when I attempt to do the join - messages about ambiguous fields, inaccurante results, etc.  Using AND and OR just added extra results.

[heavily paraphrased from original question]

You're going to have all sorts of issues like this if you don't find some way to resolve the issue of ambiguous column names - and you can do that using an AS clause which is a way of aliasing (adding and extra name) to elements of your SELECT.

Let me give you an example.   I have a table of people - parent and offspring are all people, so they're all in the same table.  I have a column for the person's ID and also another column for the parent's ID and I wish to join up the families.

Code:

mysql> select * from self; +--------+--------+------+ | parent | name   | sid  | +--------+--------+------+ |      2 | Graham |    1 | |      9 | Norman |    2 | |      3 | Lisa   |    4 | |      7 | Ruth   |    3 | |      3 | Pat    |    5 | |      3 | Phil   |    6 | |      3 | Brian  |    8 | +--------+--------+------+ 7 rows in set (0.00 sec)

Here's the problem with a join in the normal style:

Code:

mysql> select * from self join self on parent = sid; ERROR 1066: Not unique table/alias: 'self'

and here's the solution:

Code:

mysql> select * from self as child join self as parent on parent.parent = child.sid; +--------+--------+------+--------+--------+------+ | parent | name   | sid  | parent | name   | sid  | +--------+--------+------+--------+--------+------+ |      9 | Norman |    2 |      2 | Graham |    1 | |      7 | Ruth   |    3 |      3 | Lisa   |    4 | |      7 | Ruth   |    3 |      3 | Pat    |    5 | |      7 | Ruth   |    3 |      3 | Phil   |    6 | |      7 | Ruth   |    3 |      3 | Brian  |    8 | +--------+--------+------+--------+--------+------+ 5 rows in set (0.01 sec)

By giving each of the two incoming copies of the "self" table their own unique name (alias), our join condition can be uniquely specified thus overcoming the initial problem reported.  Result - a correct set of responses.

---