User of Alias on Browse SQL

[Giu]

Hi again, I don't know if i'm doing correctly or it's a limitation.

I have this query. SELECT has 2 columns pointing to the same table, and need to resolve name of each user.

Code: Select all

SELECT
 enfrentamientos.resultado,
    enfrentamientos.fecha,
    usuarionegras.sus_name as usernegras,
    usuarioblancas.sus_name as usrblancas,
    enfrentamientos.gana
FROM
    enfrentamientos
    inner join zzzzsys_user as usuarionegras on usuarionegras.zzzzsys_user_id = enfrentamientos.negras
    inner join zzzzsys_user as usuarioblancas on usuarioblancas.zzzzsys_user_id = enfrentamientos.blancas
where '#USER_ID#' IN (enfrentamientos.negras, enfrentamientos.blancas);

I can solve it just saving the name of the user too in the record, and for my needs I think it's better, but would like to know if this is possible or not for possible future needs.

Regards.

[Giu]

I tried changing to this, adding each table before the field, without aliases

Code: Select all

SELECT
 enfrentamientos.resultado,
    enfrentamientos.fecha,
    usuarionegras.sus_name,
    usuarioblancas.sus_name,
    enfrentamientos.gana
FROM
    enfrentamientos
    inner join zzzzsys_user as usuarionegras on usuarionegras.zzzzsys_user_id = enfrentamientos.negras
    inner join zzzzsys_user as usuarioblancas on usuarioblancas.zzzzsys_user_id = enfrentamientos.blancas
where '#USER_ID#' IN (enfrentamientos.negras, enfrentamientos.blancas);

Shows one field of the the record but I get:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sus_name'

===SQL===========

SELECT COUNT(*) FROM (SELECT enfrentamientos_id,enfrentamientos.fecha,usuarionegras.sus_name,usuarioblancas.sus_name,enfrentamientos.gana,enfrentamientos.resultado
 FROM     enfrentamientos     inner join zzzzsys_user as usuarionegras on usuarionegras.zzzzsys_user_id = enfrentamientos.negras     inner join zzzzsys_user as usuarioblancas on usuarioblancas.zzzzsys_user_id = enfrentamientos.blancas
 where 'globeadmin' IN (enfrentamientos.negras, enfrentamientos.blancas)) nuTCount

===BACK TRACE====

C:\UniserverXV\www\dansuprivado\core\nuform.php - line 1275 (nuRunQuery)

C:\UniserverXV\www\dansuprivado\core\nuform.php - line 456 (nuBrowseRows)

C:\UniserverXV\www\dansuprivado\core\nuapi.php - line 87 (nuGetFormObject)

[kev1n]

nuBuilder's query parser is somewhat limited. A workaround is to use a subquery by encapsulating the SQL statement within a SELECT * FROM (...)
It is important that the primary key (e.g. enfrentamientos_id) is also selected in the subquery.

Code: Select all

SELECT * FROM (
SELECT
    enfrentamientos.resultado,
    enfrentamientos.fecha,
    usuarionegras.sus_name as usernegras,
    usuarioblancas.sus_name as usrblancas,
    enfrentamientos.gana,
   enfrentamientos_id   
FROM
    enfrentamientos
    inner join zzzzsys_user as usuarionegras on usuarionegras.zzzzsys_user_id = enfrentamientos.negras
    inner join zzzzsys_user as usuarioblancas on usuarioblancas.zzzzsys_user_id = enfrentamientos.blancas
where '#USER_ID#' IN (enfrentamientos.negras, enfrentamientos.blancas)

) T

[Giu]

Thanks, will take in consideration, meanwhile I created a view.