I have a problem with sql aliases ...
I'm joining several tables two times to check for connections between persons.
in sql, the query works fine, but in retool, it does not work with the condition that the person I'm using as a starting point should not appear in the search results (AND p.name != p1.name), it seems that retool does not allow do differentiate between alias p and alias p1 – how would you handle that?
SELECT p.name, p1.name as collaborators, j.title, j.year_start, j.year_end FROM
(journal_has_person jhp
JOIN person p
ON p.person_id = jhp.person_person_id
JOIN journal j
ON j.journal_id = jhp.journal_journal_id
)
JOIN
(journal_has_person jhp1
JOIN person p1
ON p1.person_id = jhp1.person_person_id
JOIN journal j1
ON j1.journal_id = jhp1.journal_journal_id
)
WHERE j.journal_id = j1.journal_id
AND p.name != p1.name
AND (lower(p.name) LIKE lower({{'%' + textinput10.value + '%'}}))
ORDER by j.title
Hi goldsuperextra, thanks for reaching out. To help us figure out what the issue is, do you mind sending a screenshot with the error you are seeing in Retool?
hi, some background: I created a database with editors, publishers, titles etc for old scientific journals and I wrote a query that checks who collaborated with whom (was the person an editor? is there another person who edited the same journal? -> show the persons)
the query works fine in sql (screenshot 1); for retool, I use the name of the first person as a variable (screenhot 2), but retool can't handle the condition that the first and the second person should not be the same (screenshot 3). My suspicion is that Retool can't handle the aliases p and p1 - if I use this condition in Retool, the result is null.
I have to leave this condition away – so the first person is always part of the results as collaborating with himself, which does not make sense.
Screenshot1
Screenshot2
Screenshot3
I see one difference between the two queries - the use of lower() in the one used in retool. Have you tried using that exact syntax in your SQL IDE in case it has some weird side effect?
I use aliases all the time for linked tables, many linking to the same table multiple times as you do (Azure SQL) with no ill effects. Can't tell what DB you are using, but few are case sensitive (Postgres is) so maybe you can ditch the lower() if it's working in your IDE?
