Mysql Complicated Natural Join Select Statement

Question

I am trying to craft a natural join on two tables with a particular order and a particular range.

My tables

dictionary
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| wid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| word  | varchar(56)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

g219
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| pos   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| wid   | int(10) unsigned | NO   | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

dictionary has a set a dictionary of unique (case-sensitive) words and tags with an id number assigned to each. g219 is essentially a book, where pos is the position of the word/tag in the book and wid is the corresponding word id from the dictionary. I can perform a complete natural join to lay out the book as follows:

SELECT word FROM dictionary
    NATURAL JOIN g219
    ORDER BY g219.pos;

I can also apply a limit by adding LIMIT [n], but this only returns the first [N] results. I do not know how to limit this to a specified range. Both

SELECT word FROM dictionary
    NATURAL JOIN g219
    ORDER BY g219.pos
    WHERE pos BETWEEN 50 AND 100;

and

SELECT word FROM dictionary
     NATURAL JOIN g219
     ORDER BY g219.pos
     WHERE pos > 50 AND pos < 100;

fail.

Forget about the `NATURAL JOIN` construction. Specify the join columns/conditions. — jarlh, Jun 13 '18 at 12:49
See [Using MySQL LIMIT to Constrain The Number of Rows Returned By SELECT Statement](http://www.mysqltutorial.org/mysql-limit.aspx) — cdaiga, Jun 13 '18 at 12:58
I don't get the question: you ask about how to limit the result list, state that adding a `LIMIT` does not work, but do not give any examples of your tries.... — Nico Haase, Jun 13 '18 at 13:00
What I mean is limit to a range. Limit just `LIMIT [N]` just selects the first `[N]` results. I want to select a range of `pos`. — mas, Jun 13 '18 at 13:02
@jarlh is this [link](http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html) why you say to forget `NATURAL JOIN`'s? — mas, Jun 13 '18 at 13:34
@jarlh & malan Or instead of specious arguments you can understand [what natural join is for](https://stackoverflow.com/a/35034568/3404097). — philipxy, Jun 15 '18 at 02:18
@philipxy, I know how natural join works. It is a theoretical construction that should be avoided in production code. — jarlh, Jun 15 '18 at 06:53

score 2 · Accepted Answer · answered Jun 13 '18 at 12:59

2

First "WHERE", then "ORDER BY" ;)

answered Jun 13 '18 at 12:59

DataVader

740
1
5
19

Mysql Complicated Natural Join Select Statement

1 Answers1