SQL Left Outer Join does not give a full table

I was looking for similar problems but found nothing ...

I have a problem with joining two tables in SQL. The first table is created using the following procedure

DECLARE @Sequence TABLE (n DATETIME NOT NULL)
DECLARE @Index INT
SET @Index = 1
WHILE @Index <= 96 BEGIN 
    INSERT @Sequence (n) VALUES (DATEADD(Minute, @Index * 5, DATEADD(Hour, -8, '06-25-2010 00:00:00')))
    SET @Index = @Index + 1 
END

And when I run a regular query like:

SELECT
    Sequence.n
FROM
    @Sequence as Sequence 

I get what I expect - 96 rows with DateTime values ​​located at 5 minute intervals ending 06-25-2010 00:00:00 (this value will be replaced later in the SSRS report, so it may seem strange to indicate "end "range and use double DATEADD).

Now the second table contains the values ​​registered by the PLMC controllers, and they also register one record in 5 minutes (for each point identifier, but not complicating it, we can assume that there is one PointID).

, , , , 0. , 8 , . , , :

SELECT
    DataTime, DataValue
FROM
    PointValue
WHERE
    PointValue.DataTime > DATEADD(Hour, -8, '06-22-2010 00:00:00')
    AND PointValue.DataTime < '06-22-2010 00:00:00'
    AND PointID = '5284'

56 . , 20:30 .

, . , 5 , , , 96 (8 5 ) 20:30:

SELECT
    Sequence.n,
    PointValue.DataValue 
FROM
    @Sequence as Sequence LEFT OUTER JOIN PointValue 
    ON Sequence.n = PointValue.DataTime 
WHERE 
    PointID = '5280'

, - 56 , , ... Null 3,5 . , , , .

: , , Blorgbeard. , , x pointID ( ), "FROM" :

(SELECT Sequence.n, dbo.LABELS.theIndex FROM @TimeSequence as Sequence, dbo.LABELS
WHERE LEFT(dbo.LABELS.theLabel,2)='VA') as BaseTable
LEFT OUTER JOIN PointValue ON BaseTable.n = PointValue.DataTime AND BaseTable.theIndex = PointValue.PointID 

!

+3
3

, :

WHERE 
   PointID = '5280'

PointID PointValue, null , null "5280".

, , :

WHERE 
   (PointID is null) or (PointID = '5280')
+4

PointID ON - WHERE LEFT JOIN INNER JOIN:

ON Sequence.n = PointValue.DataTime AND
   PointValue.PointID = '5280'

WHERE ​​ .

+8

This is because PointID is not "5280" in your NULL strings.

Try adding this to your JOIN offer ...

ON Sequence.n = PointValue.DataTime AND PointID = '5280'
+2
source

All Articles