How to remove duplicate query values in two tables?

Asked

Viewed 1,252 times

1

I’m doing a query in two tables (students and users) and picking up the id, name and email values. Right after I do a Union between them. However, I wish there were no lines with duplicate values for the email field. I need these values because from the user’s choice the collaborative table_project will have the foreign keys fk_student or fk_user filled. The email field is for differentiating lines, because although they are different tables the id can be the same. This query is to feed a Select Multiple, where the user will tell who are the employees who will participate in the project.

Consultation

$alunos = DB::table('alunos')->where('status','Ativo')->select('email', 'nome', 'id');
$usuarios = DB::table('users')->where('status','Ativo')->select('email', 'nome', 'id');
$colaboradores = $alunos->union($usuarios);

View

{!! Form::select('colaboradores[]', $colaboradores->pluck('nome'), null, array('class' => 'form-control', 'multiple'=>'true')) !!}

Tables

Users

ID   |   nome   |   email  |   funcao   | ... |

Students

ID   |   nome   |   email  |   matricula   | ... |

collaborations_project

ID |   fk_projeto   |   fk_aluno   |   fk_usuario   |

I need help on that part, because I’ve been having this problem for a few days.

php sql laravel

  • Can post the table structure or just the sql query?

  • I entered the information Maurivan.

  • To select values from two tables you can use join or the intention and unite using union.

  • A suggestion would be to give group by email. Test and post the result.

  • There is no relationship between the tables to do the Join.

  • I believe that instead of using a "Join" to solve your problem, probably a restructuring of the tables would be better.

  • A simpler solution is to create 2 relationship tables instead of 1, since they are different things because of what you said: one user_project and another project.

Show 2 more comments

2 answers

1


In SQL, a simple but not very efficient solution for processing:

select email, nome, id from usuarios
union
select email, nome, id from alunos where email not in (select email from usuarios);

  • It worked as wanted, thank you. The way it is structured I do not see how to make consultation there very efficient.

  • One last thing: I’m having trouble with the view. {!! Form::select('contributors[]', $contributors->name, null, array('class' => 'form-control', 'Multiple'=>'true')) !! }. Give this error: Trying to get Property of non-object. There is another way but to replace select manually and foreach?

  • suggest you mark this question as answered and ask a new question better structured.

  • That one union will work, but you still do not know who is user and who is student, because turned everything id and can still return repeated ids. : select email, nome, id as fk_usuario from usuarios
union
select email, nome, id as fk_aluno from alunos where email not in (select email from usuarios);

0

Browser other questions tagged php sql laravel

You are not signed in. Login or sign up in order to post.