wake-up-neo.com

Verhindern Sie doppelte Werte in LEFT JOIN

Ich hatte eine Situation, in der ich doppelte Werte von LEFT JOIN erhielt. Ich denke, dass dies ein gewünschtes Verhalten sein kann, aber anders als ich es möchte.

Ich habe drei Tabellen:person,departmentundcontact.

Person:

id bigint,
person_name character varying(255)

Abteilung:

person_id bigint,
department_name character varying(255)

Kontakt :

person_id bigint,
phone_number character varying(255)

SQL-Abfrage:

SELECT p.id, p.person_name, d.department_name, c.phone_number 
FROM person p
  LEFT JOIN department d 
    ON p.id = d.person_id
  LEFT JOIN contact c 
    ON p.id = c.person_id;

Ergebnis:

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451"
1 |"John"     |"Finance"      |"99478"
1 |"John"     |"Finance"      |"67890"
1 |"John"     |"Marketing"    |"023451"
1 |"John"     |"Marketing"    |"99478"
1 |"John"     |"Marketing"    |"67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

Ich weiß, was Joins tun, indem sie mit ausgewählten Zeilen multipliziert bleibt. Aber es gibt ein Gefühl wie Telefonnummern 023451, 99478 __, 67890 sind für beide Abteilungen, während sie sich nur auf die Person john beziehen, mit unnötigen wiederholten Werten, die das Problem mit größeren Datenmengen eskalieren.
Also hier ist was ich will:

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451"
1 |"John"     |"Marketing"    |"99478"
1 |"John"     |""             |"67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

Dies ist ein Beispiel für meine Situation und ich verwende eine große Anzahl von Tabellen und Abfragen. Also brauche ich eine generische Lösung.

16

Ich nenne dieses Problem gerne "cross join by proxy". Da es keine Informationen gibt (WHERE oder JOIN-Bedingung), wie die Tabellen department und contact übereinstimmen sollen, werden sie über die Proxy-Tabelle person - und geben Ihnen das Cartesian-Produkt querverbunden. Sehr ähnlich diesem:

Weitere Erklärungen dort.

Lösung für Ihre Anfrage:

SELECT p.id, p.person_name, d.department_name, c.phone_number
FROM   person p
LEFT   JOIN (
  SELECT person_id, min(department_name) AS department_name
  FROM   department
  GROUP  BY person_id
  ) d ON d.person_id = p.id
LEFT   JOIN (
  SELECT person_id, min(phone_number) AS phone_number
  FROM   contact
  GROUP  BY person_id
  ) c ON c.person_id = p.id;

Sie haben welche Abteilung oder Telefonnummer nicht für die Auswahl festgelegt. Daher habe ich willkürlich das first ausgewählt. Sie können es auch anders haben ...

11

Ich denke, Sie brauchen nur Listen mit Abteilungen und Telefonen für eine bestimmte Person. Verwenden Sie also einfach array_agg (oder string_agg oder json_agg):

SELECT
    p.id,
    p.person_name,
    array_agg(d.department_name) as "department_names",
    array_agg(c.phone_number) as "phone_numbers"
FROM person AS p
LEFT JOIN department AS d ON p.id = d.person_id
LEFT JOIN contact AS c on p.id = c.person_id
GROUP BY p.id, p.person_name
2
alexpods

Obwohl die Tabellen für die Diskussion offensichtlich vereinfacht sind, scheinen sie strukturell fehlerhaft zu sein. Tabellen sollten so strukturiert sein, dass sie Beziehungen zwischen Entitäten darstellen, und nicht bloß Listen von Entitäten und/oder Attributen sind. Und ich würde eine Telefonnummer in diesem Fall als Attribut (einer Person oder Abteilung) betrachten.

Der erste Schritt wäre das Erstellen von Tabellen mit Beziehungen, die jeweils einen Primärschlüssel und möglicherweise einen Fremdschlüssel haben. In diesem Beispiel wäre es hilfreich, wenn die Personentabelle person_id für den Primärschlüssel und die Abteilungstabelle die Verwendung von department_id als Primärschlüssel verwendet. Suchen Sie dann nach Eins-zu-Viele- oder Viele-zu-Viele-Beziehungen und stellen Sie Ihre Fremdschlüssel entsprechend ein:

  • Wenn eine Person immer nur in einer Abteilung sein kann, haben Sie eine Anzahl von Personen (Personen). Kein Fremdschlüssel in der Abteilungstabelle, department_id ist jedoch ein Fremdschlüssel in der Personentabelle.
  • Wenn eine Person sich in mehreren Abteilungen befinden kann, haben Sie viele-zu-viele-Verbindungen und benötigen eine zusätzliche Junction-Tabelle mit person_id und department_id als Fremdschlüssel.

Zusammenfassend sollte es in Ihrem Szenario nur zwei Tabellen geben: eine Tabelle für die Person und die andere Tabelle für die Abteilung. Dies gilt sogar für persönliche Telefonnummern (eine Spalte in der Personentabelle) und Abteilungsnummern in der Abteilungstabelle. 

Der einzige Nachteil ist, dass eine Abteilung viele Nummern hat (oder mehrere Abteilungen eine einzige Telefonnummer teilen). Dies würde jedoch den Rahmen der ursprünglichen Frage sprengen.

1
KiloVoltaire

Verwenden Sie diese Art von Abfrage: SQL Server
(Sie können id von ORDER BY id in jede gewünschte Spalte ändern.)

SELECT 
    p.id, 
    p.person_name, 
    d.department_name, 
    c.phone_number
FROM
    person p
    LEFT JOIN 
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id) AS seq
     FROM department) d 
    ON d.person_id = p.id And d.seq = 1
    LEFT JOIN 
    ( SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id) AS seq
     FROM contact) c 
    ON c.person_id = p.id And c.seq = 1;
0
shA.t
SELECT p.id, p.person_name, d.department_name, c.phone_number 
FROM person p
  LEFT JOIN department d 
    ON p.id = d.person_id
  LEFT JOIN contact c 
    ON p.id = c.person_id 
group by p.id, p.person_name, d.department_name, c.phone_number
0
JumboClip