I want to make a query using WHERE id_user = 53 [closed]

Question

I want to make a query using WHERE id_user = 53 [closed]

Navigation

#1 by (3 votes)
#2 by (3 votes)

0

My code:

SELECT numero_empleado, nombre, paterno, materno, fec_nac, estado, genero, edociv, hijos, 
id_contact FROM grl_usuarios 
INNER JOIN grl_usuarios_contacto
ON grl_usuarios.id_usuario = grl_usuarios_contacto.id_usuario 
WHERE id_usuario = 53

ERROR: column reference "user_id" is ambiguous   LINE 3: ..._ user = grl_usuarios_contacto.id_usuario where user_id.

ERROR: column reference "user_id" is ambiguous   SQL state: 42702   Character: 231

mysql

asked by daniel macias perez 17.03.2017 в 21:40

source

2 answers

How can I count the number of characters in the "content" attribute in java? Extract part of a text that is in the middle of a string

score 3 · Answer 1

Dear, that error appears because the two tables have a column called user_id, when you place where user_id = 53, the database engine does not know if it is from the table grl_usuarios or from the table grl_usuarios_contacto. To solve this problem, I suggest you place an alias to the table. I leave a query as a suggestion. Greetings.

SELECT usr.numero_empleado, usr.nombre, usr.paterno, usr.materno, usr.fec_nac, usr.estado, usr.genero, usr.edociv, usr.hijos, 
usrCon.id_contact FROM grl_usuarios usr 
INNER JOIN grl_usuarios_contacto usrCon
ON usr.id_usuario = usrCon.id_usuario 
WHERE usr.id_usuario = 53

Or as you have solmante adding one more word to your sentence:

SELECT numero_empleado, nombre, paterno, materno, fec_nac, estado, genero, edociv, hijos, 
 id_contact FROM grl_usuarios 
INNER JOIN grl_usuarios_contacto
ON grl_usuarios.id_usuario = grl_usuarios_contacto.id_usuario 
WHERE grl_usuarios.id_usuario = 53

score 3 · Answer 2

You should add an alias to each table since you have 2 fields with the same name (In this case user_id) in this way you avoid the ambiguity of the columns.

To each column you must add an alias in this example case that I put you (a or b) as appropriate.

      select 
             a.numero_empleado
             , a.nombre
             , a.paterno
             , b.materno
             , b.fec_nac
             , b.estado
             , b.genero
             , a.edociv
             , b.hijos
             , a.id_contact 
        from grl_usuarios a
            inner join grl_usuarios_contacto b
                 on grl_usuarios.id_usuario = grl_usuarios_contacto.id_usuario 
where a.id_usuario = 53