Ottieni risultati da "combinato, entrambi inner join"

Question

Ho la sensazione che questo debba essere stato chiesto più volte, ma non riesco proprio a trovare una soluzione, o anche una domanda, probabilmente a causa della difficoltà di spiegazione.

Sappiamo tutti che inner join fondamentalmente fornisce una "intersezione" di due tabelle. E quindi più inner join danno l'intersezione di tutte le tabelle. Quello che, tuttavia, desidero è ottenere un inner join che ottenga un'intersezione della tabella B con A o della tabella C con A.

Come per una richiesta che ottiene tutte le organizzazioni che sono moderatore o amministratore.

SELECT *
FROM organization
INNER JOIN moderator_organization
  ON organization.id = moderator_organization.organization AND moderator_organization.user = 10
INNER JOIN admin_organization
  ON organization.id = admin_organization.organization AND admin_organization.user = 10

Sopra, tuttavia, selezionerebbe solo le organizzazioni da cui l'utente (con ID 10) è sia amministratore che organizzatore, non una in cui nessuna delle due è vera. Per visualizzare in un diagramma vorrei:

Dove la regione rossa è ciò che vorrei richiedere.

Answer 1

I diagrammi di Venn non sono l'ideale per visualizzare operazioni di join come ha commentato a_horse :

I diagrammi di Venn non visualizzano i join, ma impostano operazioni come unione, intersezione o esclusione.

E il suo link per illustrare:

• https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/

La svolta, credo, sia questa: il diagramma di Venn è lo strumento giusto per visualizzare il tuo obiettivo, ma l'SQL JOINè l'approccio sbagliato. Hai bisogno di un UNION(o equivalente) al centro della tua query.

Il titolo e la query di esempio sono fuorvianti in questo modo. Inoltre, inizi con:

SELECT * FROM ...

Ottiene tutte le colonne da tutte le tabelle unite. Inoltre non l'obiettivo espresso:

tutte le organizzazioni sono moderatore o amministratore.

UNION

La domanda principale è:

SELECT organization FROM admin_organization     WHERE user = 10
UNION
SELECT organization FROM moderator_organization WHERE user = 10

UNION. Non JOIN. E non UNION ALL- non vogliamo organizzazioni duplicate nel risultato.

Anche il nome della colonna organizationè fuorviante (IMHO). Dovrebbe davvero essere qualcosa di simile organization_idper chiarezza.

Il set risultante di ID univoci potrebbe già essere tutto ciò che serve. Per arricchirlo con più (o tutti) gli attributi dell'organizzazione (colonne di tabella organization), ora passiamo JOINal tavolo:

SELECT *
FROM  (
   SELECT organization AS id FROM admin_organization WHERE user = 10
   UNION
   SELECT organization FROM moderator_organization WHERE user = 10
   ) x
JOIN   organization USING (id);

In genere è (sostanzialmente) più economico applicare UNIONsolo sulla colonna ID e quindi partecipare. Potrebbe anche essere una necessità, se alcune colonne hanno tipi hanno tipi di dati senza operatore di uguaglianza. Più comune di quanto si possa pensare. Vedere:

• Come sommare i valori di una colonna json filtrata con regex?

Assumendo l'integrità referenziale, applicata con i vincoli FK, nulla viene perso nel join.

Per comodità , ha aggiunto un alias di colonna nella sottoquery ( organization AS id), in modo da poter utilizzare la condizione di join semplificata con la USING (id)quale, a sua volta, ci consente di utilizzare il semplice (e ora corretto) SELECT *nella query esterna per ottenere tutte le colonne del tabella organizationsenza una colonna ID duplicata.

Alternativa conEXISTS

Equivalente più breve che evita anche duplicazioni o eliminazioni indebite di righe:

SELECT *
FROM   organization o
WHERE  EXISTS (SELECT FROM admin_organization     WHERE user = 10 AND organization = o.id)
   OR  EXISTS (SELECT FROM moderator_organization WHERE user = 10 AND organization = o.id);

Convenzione di denominazione

Una convenzione di denominazione con nomi descrittivi può evitare confusione e rumore. Usa organization_id(o org_idse preferisci nomi brevi e non c'è ambiguità) per tutti e tre: admin_organization.organization, moderator_organization.organization, e organization.id.

Answer 2

Il diagramma di Venn che fornisci suggerisce che l'espressione a cui miri è:

(organizations JOIN moderators)
UNION
(organizations JOIN admins)

Tradotto in - il tuo - SQL

SELECT ...
FROM organization
JOIN moderator_organization
  ON organization.id = moderator_organization.organization 
  AND moderator_organization.user = 10
UNION
SELECT ...
FROM organization 
JOIN admin_organization
  ON organization.id = admin_organization.organization 
  AND admin_organization.user = 10

Si noti che le due gambe in UNION devono essere compatibili con l'unione. Ecco perché ho lasciato le colonne come un'ellisse da compilare (dal momento che non so quali siano le colonne).

Answer 3

Voglio rispondere solo a un aspetto della tua domanda iniziale, la sensazione che hai che questa domanda sia stata posta più volte. La risposta è che sì, ma la domanda è stata formulata in un modo molto diverso.

Quello che stai fondamentalmente cercando sono informazioni su organizzazioni e persone che sono collegate tra loro (forse la persona lavora per l'organizzazione). Hai l'ulteriore requisito di essere interessato solo alle persone che sono amministratori o moderatori, o entrambi.

Quando è formulata in questo modo, la domanda è stata posta in questo luogo dozzine di volte. Le persone sono sempre interessate alle informazioni sui membri di una o più sottoclassi di qualche superclasse più generale. In alcuni casi, la superclasse è costituita da veicoli e le sottoclassi sono auto, moto e camion (autocarri). In alcuni casi, la superclasse sono animali domestici e le sottoclassi sono cani, gatti e uccelli.
Nel caso di un college, la superclasse potrebbe essere costituita da persone e le sottoclassi potrebbero essere studenti, insegnanti e personale. In generale, le persone vogliono combinare le informazioni sulle sottoclassi con altre informazioni generalmente utilizzando JOIN relazionale per combinare i dati.

Altre risposte ti hanno dato un modo per formulare una query SQL che ti darà queste informazioni dato che hai archiviato le informazioni in tre tabelle: la tabella dell'organizzazione, la tabella dell'organizzazione dell'amministratore e la tabella dell'organizzazione del moderatore. Non ho miglioramenti da offrire rispetto a quelle altre risposte.

---

Quello che vorrei suggerire è un possibile design alternativo del tavolo, basato su cinque tavoli. Le cinque tabelle sono: organizzazione, amministratore, moderatore, persona e persona-organizzazione. L'organizzazione è una tabella di entità con dati sulle organizzazioni. Admin è una tabella di entità con dati sugli amministratori. Il moderatore è un'entità con dati sui moderatori.

Person è un'altra tabella di entità con dati sulle persone. Dati come nome e cognome possono essere rimossi dalla tabella dell'amministratore e del moderatore perché questi attributi sono comuni alle persone e possono invece essere archiviati nella tabella delle persone. Organization-person è una tabella di relazione con dati sulla relazione tra persone e organizzazioni, inclusi (almeno) personId e OrganizationId.

La relazione tra la tabella admin e la tabella person è quella nota come relazione IS-A. Lo stesso si può dire della tabella dei moderatori e della tabella delle persone.

Le relazioni IS-A sono spesso trascurate nei corsi introduttivi sui database, anche se si verificano sempre nel mondo reale. Sfortunatamente, le tecniche di modellazione relazionale non rendono questo tipo di situazione così semplice come vorremmo. Nella modellazione a oggetti, il modello superclasse-sottoclasse si occupa dell'intero caso e l'ereditarietà fa molto lavoro per il modellatore.

Ti rimando a tre parole d'ordine che ti daranno un'idea di questo problema di progettazione. Sono Ereditarietà tabella singola , Ereditarietà tabella classi e Chiave primaria condivisa . A seconda del tuo caso, potresti scegliere l'ereditarietà della tabella singola rispetto all'ereditarietà della tabella di classe. Ho scelto l'ereditarietà della tabella di classe nella risposta data sopra.

Se scegli l'ereditarietà della tabella di classe, ti consiglio la tecnica aggiuntiva della chiave primaria condivisa. Qui, le singole righe nella tabella admin sono identificate da personId invece di inventare un nuovo ID per la tabella admin. Lo stesso vale per il tavolo dei moderatori. Ciò rende i join necessari semplici, facili e veloci.

Se esegui ricerche di queste tre parole d'ordine, troverai molti buoni articoli sulla gestione di questo tipo di casi. Forse un giorno in futuro, le persone che tengono corsi sui database incorporeranno queste cose nella loro materia.

Ecco un link a una delle domande precedenti, con una risposta.

È una cattiva pratica avere diverse relazioni uno-a-uno che si escludono a vicenda?

Answer 4

Hai considerato l'utilizzo di outer join e una clausola where?

SELECT *
    FROM organization org
    LEFT OUTER JOIN moderator_organization mo
        ON ( org.id = mo.organization 
            AND mo.user = 10 )
    LEFT OUTER JOIN admin_organization ao
        ON ( org.id = ao.organization 
            AND ao.user = 10 )
    WHERE mo.user IS NOT NULL 
        OR ao.user IS NOT NULL ;