Home / Questions / 196614
Accepted
raintrooper
Asked: 2020-09-14 15:55:38 +0800 CST 2020-09-14 15:55:38 +0800 CST

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

  • 772

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

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

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

I've tried the following, but can't get it to 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 be like this:

Record

If anyone can help me. Very thankful.

sql-server

1 Answers

  1. Best Answer

    One way to solve it would be by means of a subquery that returns the maximum of Correlativofor 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
    • 1