Question 21288 in Mysql Question posted in
The official MySQL documentation can be found here.

Don't return repeated ids and give priority to the second table – Mysql

I have the following tables:

CREATE TABLE usuarios ( id INT, nome varchar );
INSERT INTO usuarios VALUES
(1, 'Teste'),
(2, 'Teste1'),
(3, 'Teste2'),
(4, 'Teste3'),
(5, 'Teste4'),

CREATE TABLE FichaColab( id INT, nomcompleto varchar );
INSERT INTO FichaColab VALUES
(1, 'Teste Teste'),
(3, 'Teste2 Teste2'),
(5, 'Teste4 Teste4'),

I intend to get all the names from the first table, but if the id exists in the second table, return the name of the second table instead of the name of the first.
Here is the result I want:

id nome
1 Teste Teste
2 Teste1
3 Teste2 Teste2
4 Teste3
5 Teste4 Teste4

I’m trying like this:

SELECT usuarios.id, usuarios.nome
FROM usuarios 
UNION 
SELECT FichaColab.Id, nomcompleto
FROM FichaColab

But this way returns everything from the two tables and repeats the ids and can’t.

Tags:

2

Answers


  1. 0

    This should do the job:

    select * from (
select FichaColab.* from FichaColab left join  usuarios on usuarios.id = FichaColab.id --everithing in table FichaColab 
union 
select usuarios.* from FichaColab right join usuarios on usuarios.id = FichaColab.id where FichaColab.id is null --everithing from usuarios where no record in FichaColab exists
) as unsorted
order by ID --sort it

    Demo

    Login or Signup to reply.
  2. 0

    You can use a LEFT JOIN between the two tables, then COALESCE on the name, by giving priority on the full name.

    SELECT u.id, COALESCE(fc.nomcompleto, u.nome) AS nome
FROM      usuarios   u
LEFT JOIN FichaColab fc ON u.id = fc.id

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search