How to remove duplicate query values in two tables?

1

I am doing a query in two tables (students and users) and taking the values of id, name and email. Soon after I make a union between them. However, I wanted to have no rows with duplicate values for the email field. I need these values because from the user's choice the contributors_project table will have the foreign keys fk_aluno or fk_user filled. The email field is to differentiate the 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 collaborators who will participate in the project.

Inquiry

$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   | ... |

collaborators_project

ID |   fk_projeto   |   fk_aluno   |   fk_usuario   |

I need help with this part because I already have this problem within a few days.

    
asked by anonymous 25.04.2017 / 17:39

2 answers

1

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

select email, nome, id from usuarios
union
select email, nome, id from alunos where email not in (select email from usuarios);
    
26.04.2017 / 01:32
0

Read about JOIN in the future will make your life easier and give you quicker and more efficient queries link a good links to get started.

    
08.05.2017 / 22:36