Query with the same fields in mysql

Question

I'm trying to do this query. I have these three tables projects id name

authors
    id
    id_user
    fk_id project
    type = author, editor

in Table I have authors who is the creator and who is the editor I'm trying to make a query in mysql where you can see in one record who is the author and publisher.

I do the consultation with one of the two

SELECT p.id_project,p.name_project,a.fk_id_user,a.type,u.name    
FROM projects p,users u, authors a
where p.id_project = a.fk_id_project 
and a.type = 'author' 
and a.fk_id_user = u.id_user

the result is

   id_project, name_proyect, type, user, name_user
    1 ,         bambo,       author, 145, andrea

want is to do this

id_project, name_proyect, type, user, name_user, type2, user2, name_user2
1         ,bambo        , author,145, andrea,   editor, 785,    barack

try to do this but not worked

SELECT p.id_project,p.name_project,a.fk_id_user,a.type,u.name,a.fk_id_user as user2, a.tipo as type2
FROM
projects p, users u, authors to
where p.id_project = a.fk_id_project 
and a.type = 'author' 
and a.fk_id_user = u.id_user 
and user2 = u.id_user 
and Typo2 = 'editor'

That type of query could use or if I'm doing wrong this.

Please elaborate on the phrase, "but not worked". What actually happened when you ran that query? — Dan Bracuk
– Dan Bracuk, Commented May 31, 2013 at 17:46
is projects p, users u, authors to a typo? if not you need to change authors to to authors a — Patrick Evans
– Patrick Evans, Commented May 31, 2013 at 17:49

Jack M. · Accepted Answer · 2013-05-31 20:47:41Z

1

suggest using LEFT or INNER JOIN authors a ON blablabla instead of having everything in the WHERE clause. However, try this:

SELECT
p.id_proyecto,
p.nombre_proyecto,
a.tipo,
a.fkidusario,
u.Nombres,
aa.tipo as type2
aa.fkidusario as user2,    
uu.Nombres as name_user2

FROM proyectos p 
JOIN usuarios u 
JOIN usuarios uu 
LEFT JOIN responsables a ON p.id_proyecto = a.fkidproyecto AND a.tipo = 'author' AND a.fkidusario = u.id_usuario 
LEFT JOIN responsables aa ON  p.id_proyecto = aa.fkidproyecto AND aa.tipo = 'editor' AND aa.fkidusario = uu.id_usuario

edited May 31, 2013 at 20:47

answered May 31, 2013 at 17:47

Jack M.

1,16013 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anther Over a year ago

+1 to using LEFT JOIN vs putting everything in the WHERE clause. It's tremendously easier to read. I think you should update your example to show joins in action.

Jack M. Over a year ago

Tried, but its hard without the db schema

Marztres Over a year ago

thanks for the help, the scheme is this dl.dropboxusercontent.com/u/71291454/esquema.png I did the query so dl.dropboxusercontent.com/u/71291454/consulta.PNG and the results were repeated dl.dropboxusercontent.com/u/71291454/resultado.PNG

Jack M. Over a year ago

Updated to match schema, try this :)

Collectives™ on Stack Overflow

Query with the same fields in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related