CODEWITHSUNDEEP
sqlleft-joininner-join
bircastri
bircastri

Reputation: 2169

Resultset not correct with JOIN between two table

I have this query. It works but not correctly.

SELECT 
    mc.Quantity as Production,
    mc.Data as Date,
    mc.Machine as Equipment,
    mc.DesignSpeed,
    k.Value,
    SUM(l.DurationInMinutes)/60 as DurationInMinutes
FROM
    Machine_Counter mc INNER JOIN 
    Linkup_Time l ON (mc.Data = l.ProductionDate AND mc.Machine = l.Machine) INNER JOIN 
    KPI_Value k ON (mc.Data = k.Data AND mc.Machine = k.Machine) 
WHERE 
    mc.CounterType = 1 AND 
    mc.Machine LIKE @MACHINE AND
    k.KPI = 3 AND
    mc.Data BETWEEN @DATA_START AND @DATA_END AND
    l.ReasoneCode LIKE '%7%'
GROUP BY
    mc.Quantity,
    mc.Data,
    mc.Machine,
    mc.DesignSpeed,
    k.Value
ORDER BY mc.Data

The problem is in the first INNER JOIN

Machine_Counter mc INNER JOIN 
        Linkup_Time l ON (mc.Data = l.ProductionDate AND mc.Machine = l.Machine)

Because if in the Linkup_Time table there isn't any record with 'ReasoneCode' like 7, I don't received any record.

I want the result also if in the table Linkup_Time there isn't a value in the column DurationInMinutes for that day for that Machine.

I try to use LEFT JOIN but the result is the same.

Upvotes: 0

Views: 42

Answers (2)

M.Ali
M.Ali

Reputation: 69564

You left-join is treated as an INNER JOIN when you use use l.ReasoneCode LIKE '%7%' since it is null for Machine_Counter table.

Add this condition in your ON clause for on join instead of where clause.

SELECT 
    mc.Quantity as Production,
    mc.Data as Date,
    mc.Machine as Equipment,
    mc.DesignSpeed,
    k.Value,
    SUM(l.DurationInMinutes)/60 as DurationInMinutes
FROM  Machine_Counter mc 
LEFT JOIN  Linkup_Time l ON (mc.Data = l.ProductionDate 
                        AND  mc.Machine = l.Machine
                        AND  l.ReasoneCode LIKE '%7%') 
INNER JOIN 
    KPI_Value k ON (mc.Data = k.Data AND mc.Machine = k.Machine
                AND k.KPI = 3 ) 
WHERE mc.CounterType = 1
  AND mc.Machine LIKE @MACHINE 
  AND mc.Data BETWEEN @DATA_START AND @DATA_END 
     GROUP BY
        mc.Quantity,
        mc.Data,
        mc.Machine,
        mc.DesignSpeed,
        k.Value
    ORDER BY mc.Data

Upvotes: 1

dario
dario

Reputation: 5269

This is because you are even filtering in WHERE clause a column from Linkup_Time table.

Try this:

SELECT mc.Quantity as Production
      ,mc.Data as Date
      ,mc.Machine as Equipment
      ,mc.DesignSpeed
      ,k.Value
      ,SUM(l.DurationInMinutes)/60 as DurationInMinutes
FROM Machine_Counter mc
LEFT JOIN Linkup_Time l 
    ON (mc.Data = l.ProductionDate AND mc.Machine = l.Machine) 
INNER JOIN KPI_Value k 
    ON (mc.Data = k.Data AND mc.Machine = k.Machine) 
WHERE mc.CounterType = 1 
  AND mc.Machine LIKE @MACHINE 
  AND k.KPI = 3 
  AND mc.Data BETWEEN @DATA_START AND @DATA_END
  AND (l.ReasoneCode LIKE '%7%' OR l.ReasoneCode IS NULL)
GROUP BY mc.Quantity
        ,mc.Data
        ,mc.Machine
        ,mc.DesignSpeed
        ,k.Value
ORDER BY mc.Data

Upvotes: 0

Related Questions