Select fields from another table with multiple foreign keys

Question

Select fields from another table with multiple foreign keys

Navigation

#1 by (10 votes)

8

I have 4 tables:

Lojas(nomeLoja, *IDloja*, morada, telefone, email)

Bancos(nomeBanco, *IDbanco*, morada)

PlanoContas(*conta*, descricao)

Gestao(NIB, nConta, conta1, conta2, IDloja, IDbanco, *IDgestao*)

conta1 and conta2 are foreign keys of PlanoContas , IDloja foreign key of Lojas and IDbanco foreign key of Bancos .

How can I get output similar to this:

IDgestao | NIB | nconta | nomeBanco | nomeLoja | descricao_conta1 | descricao_conta2

sql sql-server sql-server-2012

asked by anonymous 13.05.2014 / 18:16

1 answer

Subtract dates in JAVA in taking the difference of days Validate decimal numbers in Javascript

score 10 · Accepted Answer

Just make a join normal, giving names ( aliases ) to the queried tables and - in return select - referencing the specific tables and also giving names to them:

select g.IDgestao as IDgestao, g.NIB as NIB, g.nconta as nconta,
       b.nomeBanco as nomeBanco, l.nomeLoja as nomeLoja,
       c1.descricao as descricao_conta1, c2.descricao as descricao_conta2
from Gestao as g
     join Lojas as l on g.IDloja = l.IDloja
     join Bancos as b on g.IDbanco = b.IDbanco
     join PlanoContas as c1 on g.conta1 = c1.conta
     join PlanoContas as c2 on g.conta2 = c2.conta
where ...

Note: as in the from section is optional; you could simply use Gestao g , Lojas l etc. I'm not sure about using select , but it seems to me to be mandatory in that case.