CODEWITHSUNDEEP
mysqljoin
Pablohoney
Pablohoney

Reputation: 104

Natural join error

In a database in mysql I've got two views and a table, these ones:

1) PopItaNew (view)

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| Eta     | int(3)        | NO   |     | NULL    |       |
| ItaliaM | decimal(32,0) | YES  |     | NULL    |       |
| ItaliaF | decimal(32,0) | YES  |     | NULL    |       |
| ItaTot  | decimal(33,0) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

2) PopXEtaN (view)

+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| CodiceComune | int(6)     | NO   |     | NULL    |       |
| Eta          | int(3)     | NO   |     | NULL    |       |
| TotMaschi    | int(5)     | NO   |     | NULL    |       |
| TotFemmine   | int(5)     | NO   |     | NULL    |       |
| Tot          | bigint(12) | NO   |     | 0       |       |
+--------------+------------+------+-----+---------+-------+

3) and the table Decessi

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| ID           | int(5)        | NO   | PRI | NULL    | auto_increment |
| Anno         | year(4)       | NO   |     | NULL    |                |
| Mese         | int(2)        | NO   |     | NULL    |                |
| DataDecesso  | varchar(10)   | YES  |     | NULL    |                |
| Eta          | int(3)        | NO   |     | NULL    |                |
| Sesso        | enum('M','F') | NO   |     | NULL    |                |
| CodiceComune | int(6)        | NO   | MUL | NULL    |                |
| CausaInizBis | char(4)       | NO   |     | NULL    |                |
| cod_titolo   | char(5)       | NO   |     | NULL    |                |
| descr_titolo | text          | NO   |     | NULL    |                |
| cod_sez      | char(3)       | NO   |     | NULL    |                |
| descr_sez    | text          | NO   |     | NULL    |                |
| cod_cat      | char(3)       | NO   |     | NULL    |                |
| descr_cat    | text          | NO   |     | NULL    |                |
| cod_causa    | char(4)       | NO   |     | NULL    |                |
| descr_causa  | text          | YES  |     | NULL    |                |
| liv1         | text          | YES  |     | NULL    |                |
| liv2         | text          | YES  |     | NULL    |                |
| liv3         | text          | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+

from which I need everything UNTIL cod_titolo;

I've considered to get the result I want through a natural join since all views and tables have the columns CodiceComune and Eta. Thus I did the natural join writing these lines:

SELECT * FROM (PopXEtaN NATURAL JOIN PopItaNew ORDER BY CodiceComune, Eta) pop NATURAL JOIN (SELECT CodiceComune, Anno, Eta, Sesso, cod_titolo, COUNT(Eta) decessi FROM Decessi WHERE CodiceComune > 40000 AND CodiceComune < 120000 GROUP BY CodiceComune, Eta, Sesso ORDER BY CodiceComune);

But I get this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') pop NATURAL JOIN (SELECT CodiceComune, Anno, Eta, Sesso, cod_titolo, COUNT(Eta' at line 1

WHILE, If I first create two views, one by natural joining the first two views, PopItaNew and PopXEtaN and a view from Decessi, 

CREATE VIEW Pop AS SELECT * FROM PopXEtaN NATURAL JOIN PopItaNew ORDER BY CodiceComune, Eta;
CREATE VIEW Morti AS SELECT CodiceComune, Anno, Eta, Sesso, cod_titolo, count(Eta) decessi FROM Decessi WHERE CodiceComune > 40000 AND CodiceComune < 120000 GROUP BY CodiceComune, Eta, Sesso ORDER BY CodiceComune;
SELECT * FROM Pop Natural JOIN Morti;

everything works fine; do you guys have an idea on why this happens?

Upvotes: 0

Views: 339

Answers (1)

Shadow
Shadow

Reputation: 34254

Because (PopXEtaN NATURAL JOIN PopItaNew ORDER BY CodiceComune, Eta) pop is supposed to be a subquery, but it is not. In the view definitions you write it as a complete query with a select part, this is why no error is raised there.

Rewrite it as SELECT * FROM (SELECT * FROM PopXEtaN NATURAL JOIN PopItaNew ORDER BY CodiceComune, Eta) pop...

UPDATE:

Alternatively, do not define it as a subquery at all:

...FROM PopXEtaN NATURAL JOIN PopItaNew NATURAL JOIN (...) ORDER BY CodiceComune, Eta

Upvotes: 1

Related Questions