Query on sql server does not return desired result (JOINS)

Question

Query on sql server does not return desired result (JOINS)

Navigation

#1 by (1 votes)

1

I'm trying to make a query in SqlServer, but it does not display the desired result.

I have a table where I keep licenses @TblLicencias and another where I keep in which team install those licenses @TblInstalacion, increasing the correlative if that license has gone through several teams.

The problem I have is that I want to show, a list of all the existing licenses and next to show, the last place where it was installed, and if it was installed its record will be blank, just show the license.

I've tried it in the following way, but I can not make it work.

declare  @TblLicencias table  (
        IdLicencia int,
        Licencia varchar(10)
)

declare @TblInstalacion table (
    IdInstalacion int,
    IdLicencia int,
    Correlativo int,
    Destino varchar(10)
)

insert into @TblLicencias (IdLicencia,Licencia)values(1,'ABDCEFGHIJ') --2 instalaciones
insert into @TblLicencias (IdLicencia,Licencia)values(2,'JHSDHEGHIJ') --nunca instalada
insert into @TblLicencias (IdLicencia,Licencia)values(3,'ABDYTEGHIJ') --1 instalación

insert into @TblInstalacion (IdInstalacion,IdLicencia,Correlativo,Destino)values(1,1,1,'OFICINA 1')
insert into @TblInstalacion (IdInstalacion,IdLicencia,Correlativo,Destino)values(2,1,2,'OFICINA 2')
insert into @TblInstalacion (IdInstalacion,IdLicencia,Correlativo,Destino)values(3,3,1,'SALA 1')

--SELECT *
--FROM @TblLicencias a
--LEFT JOIN @TblInstalacion b ON a.IdLicencia = b.IdLicencia
--WHERE a.IdLicencia IN (
--      SELECT MAX(Correlativo),IdLicencia
--      FROM @TblInstalacion
--      GROUP BY IdLicencia
--      )

--SELECT *
--FROM @TblLicencias a
--LEFT JOIN @TblInstalacion b ON a.IdLicencia = b.IdLicencia
--LEFT JOIN (
--      SELECT MAX(Correlativo) as Contador
--      FROM @TblInstalacion c   
--      GROUP BY IdLicencia
--      )c on b.Correlativo=c.Contador

The correct result should look like this:

If someone, can you help me. Very grateful.

sql-server

asked by raintrooper 14.09.2018 в 01:55

source

1 answer

How to fix error Add-Migration connectionstring null Align ul adjusting high

score 1 · Accepted Answer

One way to solve it, would be by means of a subquery that delivers the maximums of Correlativo for each IdLicencia , for example:

SELECT  a.IdLicencia,
        c.Correlativo,
        c.Destino
        FROM @TblLicencias a
        LEFT JOIN (SELECT   IdLicencia,
                MAX(Correlativo) AS MaxCorrelativo
            FROM @TblInstalacion
                        GROUP BY IdLicencia
        ) b 
             ON a.IdLicencia = b.IdLicencia
        LEFT JOIN @TblInstalacion c
             ON c.IdLicencia = a.IdLicencia
             AND c.Correlativo = b.MaxCorrelativo