How can solve duplicate values in sql

Question

How can i write this sql queries? Results have same values, i want to see below

Hotspot_Adi | Referans_Numarasi | Il |  Bolge | CA     | ADETI |   KYS    | ADET
A           | xxxx              | y  |  z     | AP     | 2     |   AP     |    2
A           | xxxx              | y  |  z     | Modem  | 1     |   Modem  |    1

but i see

Hotspot_Adi | Referans_Numarasi | Il |  Bolge | CA     | ADETI |   KYS    | ADET
A           | xxxx              | y  |  z     | AP     | 2     |   AP     |    2
A           | xxxx              | y  |  z     | Modem  | 1     |   Modem  |    1
A           | xxxx              | y  |  z     | Modem  | 1     |   AP     |    2
A           | xxxx              | y  |  z     | AP     | 2     |   Modem  |    1

My query is;

SELECT a.Hotspot_Adi, a.Referans_Numarasi, a.Il, a.Bolge, a.CA, a.ADETI, b.KYS, b.ADET 
FROM 
( 
 SELECT t1.Hotspot_Adi, t1.Referans_Numarasi, st.symbol AS Il ,
        Bol.sym AS Bolge, t1.Envanter_Sinif AS CA, 
        Count(t1.Envanter_Sinif) AS ADETI
 FROM  mdb.dbo.zODYA_EnvanterBilgi AS t1
 INNER JOIN mdb.dbo.ca_location ON t1.Referans_Numarasi =mdb.dbo.ca_location.zReferansNo          
 INNER JOIN mdb.dbo.ca_state_province AS st ON mdb.dbo.ca_location.state = st.id 
 INNER JOIN mdb.dbo.usp_contact AS t3 ON mdb.dbo.ca_location.zReferansNo = t3.zReferansNo 
 INNER JOIN mdb.dbo.zODYA_BolgeMudurlukleri AS Bol ON t3.zODYA_BolgeMudurlukleriID = Bol.id 
 WHERE (t1.Envanter_Sinif like '%Access%' 
        or 
        t1.Envanter_Sinif like '%Modem%'
        or t1.Envanter_Sinif like '%Switch%'
       )
GROUP BY t1.Hotspot_Adi, t1.Envanter_Sinif, t1.Referans_Numarasi, st.symbol, Bol.sym
)AS a
INNER JOIN
(
 SELECT t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif AS KYS, 
        Count(t5.Envanter_Sinif) AS ADET
 FROM mdb.dbo.zODYA_EnvanterBilgi AS t5
 INNER JOIN NetPerfMon.dbo.Nodes AS t2 ON t5.Seri_Numarasi = t2.Serial_Number
 WHERE (t5.Envanter_Sinif like '%Access%' 
        or 
        t5.Envanter_Sinif like '%Modem%' 
        or t5.Envanter_Sinif like '%Switch%'
        )
 GROUP BY t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif
) AS b
ON a.Referans_Numarasi = b.Referans_Numarasi

How should i do? Thank you.

Answer 1

Using only Referans_Numarasi you have a partial cartesian product.

You need complete your join : a.CA and b.KYS

SELECT
    a.Hotspot_Adi,
    a.Referans_Numarasi,
    a.Il,
    a.Bolge,
    a.CA,
    a.ADETI,
    b.KYS,
    b.ADET 
FROM
(
    SELECT
        t1.Hotspot_Adi, t1.Referans_Numarasi, st.symbol AS Il , Bol.sym AS Bolge, t1.Envanter_Sinif AS CA, Count(t1.Envanter_Sinif) AS ADETI
    FROM
        mdb.dbo.zODYA_EnvanterBilgi AS t1
        INNER JOIN mdb.dbo.ca_location ON t1.Referans_Numarasi = mdb.dbo.ca_location.zReferansNo          
        INNER JOIN mdb.dbo.ca_state_province AS st ON mdb.dbo.ca_location.state = st.id 
        INNER JOIN mdb.dbo.usp_contact AS t3 ON mdb.dbo.ca_location.zReferansNo = t3.zReferansNo 
        INNER JOIN mdb.dbo.zODYA_BolgeMudurlukleri AS Bol ON t3.zODYA_BolgeMudurlukleriID = Bol.id    
    WHERE
        (t1.Envanter_Sinif like '%Access%' or t1.Envanter_Sinif like '%Modem%' or t1.Envanter_Sinif like '%Switch%')
    GROUP BY
        t1.Hotspot_Adi, t1.Envanter_Sinif, t1.Referans_Numarasi, st.symbol, Bol.sym
) AS a
    INNER JOIN
(
    SELECT
        t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif AS KYS, Count(t5.Envanter_Sinif) AS ADET
    FROM
        mdb.dbo.zODYA_EnvanterBilgi AS t5
        INNER JOIN NetPerfMon.dbo.Nodes AS t2 ON t5.Seri_Numarasi = t2.Serial_Number
    WHERE
        (t5.Envanter_Sinif like '%Access%' or t5.Envanter_Sinif like '%Modem%' or t5.Envanter_Sinif like '%Switch%')
    GROUP BY
        t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif
) AS b
    ON
    a.Referans_Numarasi = b.Referans_Numarasi and a.CA and b.KYS