I am trying to write a query for a MySQL database which compares two tuples within the same relation that is generated by a natural join.
The database has relations author and author_number, which can be natural joined to create a relation that shows all authors as well as all of their phone numbers.
I am trying to write a query that returns only tuples for authors that share the same phone number.
I have written a query that I believe has the right idea behind it, but I have done something wrong because I am getting a syntax error when I try to run it.
Here is the query I currently have which I thought would work:
SELECT A.first_name, A.last_name, A.pNumber
FROM (author NATURAL JOIN author_number AS A), (author NATURAL JOIN author_number AS B)
WHERE A.pNumber = B.pNumber;
The query is giving the error:
ERROR 1066 (42000) at line 60 in file: 'QueryLib.sql': Not unique table/alias: 'author'
Also my original query didn't have the A.first_name, etc. in the SELECT clause, it just had first_name, etc. but I got the same error message.
First note that natural joins are considered harmful: Is NATURAL (JOIN) considered harmful in production environment?. It is better to write an explicit inner join.
Returning to your question. You need to refer to individual tables from the natural join. For example:
SELECT
A1.first_name, A1.last_name, AN1.phone_number
FROM
Author A1 NATURAL JOIN AuthorNumber AN1,
Author A2 NATURAL JOIN AuthorNumber AN2
WHERE
AN1.phone_number = AN2.phone_number AND
NOT (A1.first_name = A2.first_name AND A1.last_name = A2.last_name);