Select same columns-CodePudding

These are the two table I'm using :

CREATE TABLE [dbo].[AGENT](
[MATRICULE] [varchar](15) NOT NULL,
[CIVILITE] [varchar](5) NULL,
[NOM] [varchar](30) NULL, 
[PRENOM] [varchar](30) NULL, 
[DT_NAISSANCE] [datetime] NULL,
[LIEU_NAISSANCE] [varchar](30) NULL,
DROIT_A_LA_PAIE [smallint] NULL )

CREATE TABLE [dbo].[AFFECTATION](
[MATRICULE] [varchar](15) NOT NULL REFERENCES AGENT(MATRICULE),
[STE] [varchar](8) NULL,
[ETB] [varchar](8) NULL,
[SCE] [varchar](8) NULL,
[LIEU] [varchar](8) NULL,
[MATRIC_SUPERIEUR] [varchar](15) NULL REFERENCES AGENT(MATRICULE),
[CENTRE_GESTION] [varchar](12) NULL REFERENCES AGENT(MATRICULE),
CONSTRAINT [PK_AFFECTATION] PRIMARY KEY CLUSTERED)

I want to select the Matricule , Nom , Prenom , Matric_superieur , Nom , Prenom , Centre_Gestion , Nom, Prenom

CodePudding user response：

you need to do a join

And with SELECT you can do :

SELECT AGENT.MATRICULE AFFECTATION.MATRICULE AGENT.NOM AFFECTATION.NOM etc.

CodePudding user response：

That is what JOIN clauses are for... What is the relationship between the first table and the second. Since the same column name "nom" exists in both tables, you would use the table.column AS and give an alternate name for it.

select
      ag1.Matric_Superieur,
      ag1.nom MatricNom,
      ag1.prenom MatricPrenom,
      ag2.Centre_Gestion,
      ag2.nom CentreNom,
      ag2.prenom CentrePrenom
   from
      Affectation af
         join Agent ag1
            on af.matric_superieur = ag1.Martricule
         join Agent ag2
            on af.centre_gestion  = ag2.Martricule

Notice the "ag1.nom MatricNom". This means take the column ag1 (alias of the first instance of the Agent joined table), column "nom" and return it as a final column name "MatricNom" because the first join to the agent table is based on the MATRIC_SUPERIEUR id key. Similar for prenom. Then ag2 alias since that is the SECOND instance joined to the agent table by its respective key.