jason jason - 1 month ago 9
SQL Question

Why does this query have two selects?

I have this query :

SELECT WorkId, RegisterDate, sum(RoomType1) As RoomType1, sum(RoomType2) As RoomType2, sum(RoomType3) As RoomType3, sum(RoomType4) As RoomType4, sum(RoomType5) As RoomType5, sum(RoomType6) As RoomType6, sum(RoomType7) As RoomType7, sum(RoomType8) As RoomType8
FROM (

SELECT dbo.[Work].WorkId, dbo.[Work].RegisterDate,

case dbo.Floor.RoomType when 1 then 1 else 0 end as RoomType1,
case dbo.Kat.RoomType when 2 then 1 else 0 end as RoomType2,


FROM dbo.Belediye INNER JOIN
dbo.[Is] ON dbo.Municipality.MunicipalityId= dbo.[Is].MunicipalityWorkId INNER JOIN
dbo.Look ON dbo.[Work].LookWorkId = dbo.Look.LookId ,
WHERE (dbo.Look.LocationIS NOT NULL)

) E
GROUP BY WorkId,


This query works as expected, but I can't understand why it has two selects, why does it need them? Please explain it to me. Thanks.

Answer

As you suspected this query dont need two selects and could be rewritten without sub-query:

SELECT  i.IsId, 
        i.KayitTarihi, 
        SUM(case k.OdaTipi when 1 then 1 else 0 end) as RoomType1,
        SUM(case k.OdaTipi when 2 then 1 else 0 end) as RoomType2,
        SUM(case k.OdaTipi when 3 then 1 else 0 end) as RoomType3,
        SUM(case k.OdaTipi when 4 then 1 else 0 end) as RoomType4,
        SUM(case k.OdaTipi when 5 then 1 else 0 end) as RoomType5,
        SUM(case k.OdaTipi when 6 then 1 else 0 end) as RoomType6,
        SUM(case k.OdaTipi when 7 then 1 else 0 end) as RoomType7,
        SUM(case k.OdaTipi when 8 then 1 else 0 end) as RoomType8
FROM dbo.Belediye b
INNER JOIN dbo.[Is] i
    ON b.BelediyeId = i.BelediyeIsId 
INNER JOIN dbo.YerGorme yg
    ON i.YerGormeIsId = yg.YerGormeId 
INNER JOIN dbo.Kat k
    ON yg.YerGormeId = k.YerGorme_YerGormeId
WHERE yg.Lokasyon IS NOT NULL
GROUP BY i.IsId, i.KayitTarihi

Note: use table aliases