0

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.

Yang
  • 7,712
  • 9
  • 48
  • 65
Kyle Hobbs
  • 445
  • 1
  • 4
  • 14
  • 1
    You are mixing implicit join and natural join in a weird manner, this giving syntax errors. Give sample data in the two tables, and correspondence expected output. And if problem is solvable by regular JOIN, i would go with them.. – Madhur Bhaiya Jul 02 '19 at 03:18
  • 1
    Read [the MySQL manual re join grammar & semantics](https://dev.mysql.com/doc/refman/8.0/en/join.html). The aliases here are author (implicitly), A, author (implicitly) & B. One can't use the same alias twice. A & B alias author_number. Seems maybe you want (SELECT * FROM author NATURAL JOIN author_number) AS A & similarly for B. PS Please read & act on [mre]. PS Normally when using NATURAL JOIN we would expect to rename columns via a subselect. Aliases are for non-NATURAL JOINs. PS One doesn't need to use both NATURAL & non-NATURAL JOINs to do what you want. – philipxy Jul 02 '19 at 10:14

1 Answers1

2

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);

Demo: http://sqlfiddle.com/#!9/ba2951/7.

Yang
  • 7,712
  • 9
  • 48
  • 65
  • 1
    Please note that the people who consider Natural JOIN harmful are the people who don't understand the Relational Model, and aren't competent to design schemas for it. – AntC Jul 02 '19 at 21:30