SQL Server Performance Forum – Threads Archive

Error in query

Hi, I’m new in Sql Server (please be gentil if you see something stupid in my question). I have problems with this query: SELECT personal.admin.modulos.id,
personal.admin.modulos.nombre,
personal.admin.modulos.descripcion,
personal.admin.usuarios.id
FROM personal.admin.modulo_perfil INNER JOIN personal.admin.modulos ON (personal.admin.modulo_perfil.id_modulo = personal.admin.modulos.id)
INNER JOIN personal.admin.perfiles ON (personal.admin.modulo_perfil.id_perfil = personal.admin.perfiles.id)
CROSS JOIN personal.admin.perfil_usuario
INNER JOIN personal.admin.usuarios ON (personal.admin.perfil_usuario.id_usuario = personal.admin.usuarios.id )
INNER JOIN personal.admin.perfiles ON (personal.admin.perfil_usuario.id_perfil = personal.admin.perfiles.id )
WHERE (personal.admin.usuarios.login = ‘xxxxxxxx’) it returns this error: "Tables or functions ‘personal.admin.perfiles’ and ‘personal.admin.perfiles’ have the same exposed names. Use correlation names to distinguish them." Any Ideas?

---

Hi ya, if your connected as the user called admin to the database called personal, then you can leave off the personal.admin. bit from all the tables. the error itself means that you can’t have the same named table in the query twice without using an alias (a.k.a. correlation name) So change the query to
SELECT m.id,
m.nombre,
m.descripcion,
u.id
FROM personal.admin.modulo_perfil mp
INNER JOIN personal.admin.modulos m
ON (mp.id_modulo = m.id)
INNER JOIN personal.admin.perfiles p 1
ON (mp.id_perfil = p1.id)
CROSS JOIN personal.admin.perfil_usuario pu
INNER JOIN personal.admin.usuarios u
ON (pu.id_usuario = u.id )
INNER JOIN personal.admin.perfiles p2
ON (pu.id_perfil = p2.id )
WHERE (u.login = ‘xxxxxxxx’)
Cheers
Twan

---

Twan, thanks for your answer (really faster) It works very good now. Regards

---

]]>