Syntax problem for a newbie

"Fred" <frederic.fleche@gmail.com> writes:

I have a syntax problem but I don't find the clue.
Actually I adapt an mySQL query to a postgreSQL but I got a message
error that I can't interpret.

SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
subpath,l.id-1 as level
FROM graph_path g
INNER JOIN term AS t1
INNER JOIN term AS t2 ON (t2.id = g.term2_id)
INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
AND g.distance+1 >= l.id)
WHERE t2.name = 'blood_coagulation' and g.term1_id=1
ORDER BY g.id, subpath;

You're short an ON condition: there has to be an ON for every JOIN.
Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON.

Does MySQL really accept that as-is? (Standards compliance was never
their strong point :-()

regards, tom lane

Fred wrote:

Dear all,

I have a syntax problem but I don't find the clue.
Actually I adapt an mySQL query to a postgreSQL but I got a message
error that I can't interpret.

SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as

The thing that stands out to me is the syntax for the substring
function, see:
http://www.postgresql.org/docs/8.1/static/functions-string.html

Hope this helps,

--

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote:

"Fred" <frederic.fleche@gmail.com> writes:

I have a syntax problem but I don't find the clue.
Actually I adapt an mySQL query to a postgreSQL but I got a message
error that I can't interpret.

SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
subpath,l.id-1 as level
FROM graph_path g
INNER JOIN term AS t1
INNER JOIN term AS t2 ON (t2.id = g.term2_id)
INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
AND g.distance+1 >= l.id)
WHERE t2.name = 'blood_coagulation' and g.term1_id=1
ORDER BY g.id, subpath;

You're short an ON condition: there has to be an ON for every JOIN.
Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON.

Does MySQL really accept that as-is? (Standards compliance was never
their strong point :-()

Yes, MySQL (4.1.14) quite happily accepts that as-is...

--

Russ

Russ Brown <pickscrape@gmail.com> writes:

On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote:

"Fred" <frederic.fleche@gmail.com> writes:

SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
subpath,l.id-1 as level
FROM graph_path g
INNER JOIN term AS t1
INNER JOIN term AS t2 ON (t2.id = g.term2_id)
INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
AND g.distance+1 >= l.id)
WHERE t2.name = 'blood_coagulation' and g.term1_id=1
ORDER BY g.id, subpath;

Does MySQL really accept that as-is? (Standards compliance was never
their strong point :-()

Yes, MySQL (4.1.14) quite happily accepts that as-is...

[ tries it... ] Hm, 5.0.x is no better. This is really bad, because it
shows that they completely misimplemented the JOIN syntax. The above
query is ambiguous because it's not clear which JOIN each ON is supposed
to go with. Per spec, you can write something like

FROM a JOIN b JOIN c ON b_c_cond ON a_bc_cond

which is supposed to be parenthesized as

FROM a JOIN (b JOIN c ON b_c_cond) ON a_bc_cond

so that the conditions are associated with the joins I named them after.
If you parse things so that ON is optional then it's completely unclear
which JOINs the ONs that are there are supposed to go with. And this
matters, particularly for outer joins.

mysql> select * from a join b left join c on (b1=c1) on (a1=b1);
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 'on (a1=b1)' at line 1

Wonderful :-(

regards, tom lane