jason jason - 11 months ago 43
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

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

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 Source

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

SELECT  i.IsId, 
        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
GROUP BY i.IsId, i.KayitTarihi

Note: use table aliases