Data-where condition with LEFT OUTER JOIN sql

[jmdc]

Hello,

I am trying to have a left outer join in a data-where condition (databasejoin element) whith this code:

SELECT Nome_completo FROM esc_alunos
LEFT OUTER JOIN inscriptions
ON esc_alunos.userid = inscriptions.userid and esc_alunos.Nome_completo = inscriptions.Alunoainscrever
WHERE inscriptions.Alunoainscrever IS null and Userid={$my->id} and EstadoAluno = 2

but it returns an error in frontend when i try to insert a new row (list view) like:

Warning: Header may not contain more than a single header, new line detected in /home/inscri/public_html/libraries/joomla/application/web.php on line 960

1064 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 'SELECT Nome_completo FROM esc_alunos LEFT OUTER JOIN inscriptions ON esc_aluno' at line 3 SQL=SELECT DISTINCT(`esc_alunos`.`Nome_completo`) AS value, CONCAT_WS('', NumeroAluno, ' - ', Nome_completo) AS text FROM `esc_alunos` AS `esc_alunos` WHERE SELECT Nome_completo FROM esc_alunos LEFT OUTER JOIN inscriptions ON esc_alunos.userid = inscriptions.userid and esc_alunos.Nome_completo = inscriptions.Alunoainscrever WHERE inscriptions.Alunoainscrever IS null and Userid=135 and EstadoAluno = 2 ORDER BY text ASC


Thank you in advance.

 

[jmdc]

I solved that using this code:

WHERE {thistable}.`Nome_completo`
IN ( SELECT Nome_completo FROM esc_alunos
LEFT OUTER JOIN inscriptions
ON esc_alunos.userid = inscriptions.userid and esc_alunos.Nome_completo = inscriptions.Alunoainscrever
WHERE inscriptions.Alunoainscrever IS null and esc_alunos.Userid = {$my->id} and EstadoAluno = 2)

Thanks anyway!!

 

[cheesegrits]

Thanks for posting the solution. Not sure what is wrong with that first query, looks kosher to me on first glance.

-- hugh