Querying the same table twice

[GuitarGod]

Hi everyone. I'm a query very similar to the one below with no success. Unfortunately SQLs and joins are not my strong suit.

 

SELECT f.field1, f.field2, f.field3
FROM fields_table f
WHERE f.field1 = SOME_VALUE
  LEFT JOIN fields_table s
  ON s.field2 > f.field2
  AND s.field3 <= f.field3
    LIMIT 1

 

I would like the first SELECT query to be returned even if there are no matches on the join, also I'd like the second table query to be limited to 1. Is this possible? Could anyone revise the table above to make this work?

 

Thanks!

[scootstah]

I think you want a right join instead of a left join.

 

Also, you can't limit the join to less than the overall rows returned. If you return 5 rows but only 1 row matches the join, the rest will just be NULL.

[GuitarGod]

Using LEFT or RIGHT seems to leave me with a sytax error. It seems to point to using AND after the ON statement, could this be why?

[scootstah]

I believe it is because the WHERE clause has to come after any JOINs.

[batwimp]

JOINS need to be in the FROM section, not the WHERE section.