Início / dba / Perguntas / 312757
Accepted
Martin
Asked: 2022-06-01 03:08:24 +0800 CST

MariaDb - Como garantir um (LEFT) JOIN somente se existir um único registro do lado direito

  • 772

Eu tenho duas tabelas que preciso juntar. No entanto, eu só quero unir registros quando houver correspondências incomuns, em vez de escolher um dos vários para a junção.

Versão:

Usando MariaDB versão 10.3.34

Dados de exemplo:

Dados principais (ESQUERDA)towns

Eu iria Cidade código postal
1 Hudderfield HD11 4ER
2 Manchester MN14 3JE
3 Macklesfield MK17 9FL
4 Edimburgo ED5 3MJ
5 Liverpool LV9 8XT

Dados combinados (DIREITO) peoples:

Eu iria nomes código postal
1 Jimmy Saville HD11 4ER
2 Jason Bomb IP14 8FK
3 Mickey Mouse MK17 9FL
4 Bobby Dillian ED5 3MJ
5 Lenny Davies ED5 3MJ

Meu SQL:

Minha consulta inicial seria algo como:

SELECT towns.id, towns.town, peoples.name FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode

Mas isso incluirá, Edinburghmas há duas pessoas em Edimburgo, eu só quero participar quando houver uma única linha incomum para participar.

Eu uso a junção LEFT porque preciso retornar todas towns, mas apenas as linhas exclusivas de peoples.

Resultados esperados:

Eu iria Cidade nomes
1 Hudderfield Jimmy Saville
2 Manchester <null>
3 Macklesfield Mickey Mouse
4 Edimburgo <null>
5 Liverpool <null>

O que eu tentei

Eu tentei usar COUNT()no JOIN, mas não consigo fazer isso funcionar,

SELECT towns.id, towns.town, peoples.names FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode AND count(peoples.id) = 1

Apresenta um erro de sintaxe.

Não consigo pensar em como posso qualificar essa junção de que ela só se une quando há um único resultado encontrado. A pesquisa na Internet me dá muitas referências muito mais vagas e fora do tópico.

Tenho certeza que é simples, mas não consigo. Além disso, gostaria de evitar subconsultas, se possível?

violino de banco de dados

join mariadb

4 respostas

  1. Best Answer

    Uma maneira alternativa para a resposta da função de janela de Lennart é apenas usar uma cláusula GROUP BYand contra a tabela para filtrar aquelas com o mesmo :HAVINGpeoplespostcode

    SELECT towns.id, towns.town, peoples.names
FROM towns
LEFT JOIN
(
    SELECT MAX(names) AS names, postcode
    FROM peoples
    GROUP BY postcode
    HAVING COUNT(name) = 1
) peoples
ON towns.postcode = peoples.postcode
    • 6

  2. Você deve ser capaz de se juntar a pessoas com:

    (select name, postcode
from (
  select name, postcode, count(1) over (partition by postcode) as cnt
  from peoples
) as t
where cnt = 1)

    ou seja

    SELECT t.id, t.town, p.name 
FROM towns t
LEFT JOIN (SELECT name, postcode
           FROM (
               SELECT name, postcode
                    , count(1) over (partition by postcode) as cnt
               FROM peoples
           ) as x
           WHERE cnt = 1
) p
    USING (postcode)

    EDITAR:

    Dado o ddl fornecido na atualização, criei o db<>fiddle

    SELECT t.id, t.town, p.names 
FROM towns t
LEFT JOIN (
    SELECT names, postcode
    FROM (
       SELECT names, postcode
            , count(1) over (partition by postcode) as cnt
       FROM peoples
    ) as x
    WHERE cnt = 1
) p
    USING (postcode);

    Parece dar o resultado esperado

    • 5

  3. Ainda mais simples:

    SELECT t.id, t.town, 
       IF (COUNT(DISTINCT p.names) = 1, MAX(p.names), NULL) AS names
    FROM towns AS t
    LEFT JOIN peoples AS p  ON t.postcode = p.postcode 
    GROUP BY t.id

    Acredito que evita o problema "somente grupo completo por" mencionado nos comentários anteriores. (Se não, veja os comentários sobre esta resposta.)

    • 5

  4. Olha, mãe, sem subconsultas e funções agregadas ou de janela!

    SELECT t.id
     , t.town
     , IF( p2.id IS NOT NULL, NULL, p1.names ) AS names
  FROM towns AS t
  LEFT JOIN peoples AS p1  ON p1.postcode =  t.postcode
  LEFT JOIN peoples AS p2  ON p2.postcode = p1.postcode
                          AND p2.id != p1.id
 GROUP BY t.id
;
    • 2

relate perguntas