There is a request, I tried to make it ...

SELECT "news".id,"news".uri,"news".title,"news".date, replace( replace( replace( replace("news".uri,'http://www...ru/gal.php?cat=',''), 'http://www...ru/gal.php?pr=',''), 'http://...ru/gal.php?pr=', '' ),'http://www...ru/pr.php','' )::integer AS photorep FROM news W1 JOIN pr_photoreport W2 ON W2.id = W1.photorep WHERE "news".uri <> ''

In general, it is necessary that he did not swear. in the line ON W2.id = W1.photorep it cannot understand why photorep does not exist, how can this be corrected?

  • 3
    Well, write ON replace( replace( replace( replace(W1.uri,'http://www...ru/gal.php?cat=',''), 'http://www...ru/gal.php?pr=',''), 'http://...ru/gal.php?pr=', '' ),'http://www...ru/pr.php','' )::integer = w2.id he probably does not understand aliases from the list of select fields - Sergey
  • one
    And not likely, but for sure. synonyms from the select list cannot be used in ON or WHERE. Either copy the entire replace to ON, or wrap it in another select, where this synonym will be a regular column and join to it. In general, if you have to glue the tables on such a terrible condition - then you have not correctly designed the database - Mike
  • These previous developers could not normally associate one object with another, and connected it all by means of regular links ... and not by IDs, which would be more logical. - Nekita

0