Join two table with two different Foreign Key for get same field

Catanzaro

in my exercise for Oracle/Sql, i have 2 table D_REL_ENTI_FABBRICATI and D_ENTI:

D_REL_ENTI_FABBRICATI has 2 FK: FK_ENTI and FK_ENTE_PROPRIETARIO and 2 field that i want get: Denominazione and Codice_Fiscale. D_ENTI has unique Primary Key: SEQU_ENTE.

Now i want select all record of D_REL_ENTI_FABBRICATI with 4 fields:

  • Amm_Dich_Denom: get Denominazione when D_REL_ENTI_FABBRICATI.FK_ENTI=D_ENTI.SEQU_ENTE;

  • Amm_Dich_CF: get Codice_Fiscale when D_REL_ENTI_FABBRICATI.FK_ENTI=D_ENTI.SEQU_ENTE;

  • Ente_Prop_Denom: get Denominazione when D_REL_ENTI_FABBRICATI.FK_ENTE_PROPRIETARIO=D_ENTI.SEQU_ENTE;

  • Ente_Prop_CF: get Codice_Fiscale when D_REL_ENTI_FABBRICATI.FK_ENTE_PROPRIETARIO=D_ENTI.SEQU_ENTE;

my solution is this:

select 
D.FK_ENTE,
D.FK_ENTE_PROPRIETARIO,
(select D_ENTI.DESC_DENOMINAZIONE from D_ENTI where D.FK_ENTE=D_ENTI.SEQU_ENTE) AMM_DICH_DENOM,
(select D_ENTI.CODI_CODICE_FISCALE from D_ENTI where D.FK_ENTE=D_ENTI.SEQU_ENTE) AMM_DICH_CF,
(select D_ENTI.DESC_DENOMINAZIONE from D_ENTI where D.FK_ENTE_PROPRIETARIO=D_ENTI.SEQU_ENTE) ENTE_PROPR_DENOM,
(select D_ENTI.CODI_CODICE_FISCALE from D_ENTI where D.FK_ENTE_PROPRIETARIO=D_ENTI.SEQU_ENTE) ENTE_PROPR_CF
from 
D_REL_ENTI_FABBRICATI D

But there is another method more elegant and afficent?

Thanks all Regards

Koen Lostrie

Is this what you're looking for ?

SELECT
  d.fk_ente,
  d.fk_ente_proprietario,
  de.desc_denominazione      amm_dich_denom,
  de.codi_codice_fiscale     amm_dich_cf,
  dep.desc_denominazione     ente_propr_denom,
  dep.codi_codice_fiscale    ente_propr_cf
  FROM
  d_rel_enti_fabbricati  d
    LEFT OUTER JOIN d_enti de ON d.fk_ente = de.sequ_ente
    LEFT OUTER JOIN d_enti dep ON d.fk_ente_proprietario = dep.sequ_ente

If you always have values in d.fk_ente and d.fk_ente_proprietario you can replace the LEFT OUTER JOIN with JOIN

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

join on two foreign keys from same table in SQL

inner join two foreign key in one table

Two foreign Key of same table in one table in sequelize

Join two rows by different ids from a same table

MySQL two foreign key from same table

Laravel have Two foreign Key from the same table

how to join two table only when foreign key value is not null

Entity Framework - Main table with two users with different name field, relation with a single field of foreign key

SQL Join two table with key into different column

How to name two columns in join tables with two same foreign key at one table in SQL?

How to join two tables with same primary key name but different values

Join on two of the same foreign keys

SQL - Join two fields in the same table to a single field in another table

Foreign key contraints in a table on two columns referncing a same table

Can I use the same foreign key constraint in two different tables?

join two columns with same title but in different table

Left Join the same table based on two different fields?

Joining two tables with one table having a different foreign key

Join on two table with same primary key but different data

Two columns in same table and same foreign key

INNER JOIN for two columns of the same foreign key

MySQL Using Same Foreign key for two different table columns

join with two columns and get field values from their foreign key with single sql query

Join Table Twice Each With Two Different Values for Same Field

Django: join two table on foreign key to third table?

Select name of two foreign keys referring to same primary key table

django ORM join two model on a foreign key

how Inner join work on two foreign key from single table

How to make a foreign key within two columns is the same table?