Left outer join in SQL Server 2012 LocalDB not working?

Question

Using SQL Server 2012 (LocalDB), I have three tables:

BESEXT.COMPUTER
BESEXT.ANALYSIS_PROPERTY
BESEXT.ANALYSIS_PROPERTY_RESULT

These contains following info:

BESEXT.COMPUTER: Mapping between ComputerIDs and ComputerNames
BESEXT.ANALYSIS_PROPERTY: List of properties that can be mapped to a computer
BESEXT.ANALYSIS_PROPERTY_RESULT: List of values of properties for a computer

First, I perform the following query:

SELECT
    AR.ComputerID,
    AP.Name,
    AR.Value
FROM BESEXT.ANALYSIS_PROPERTY_RESULT AR
JOIN BESEXT.ANALYSIS_PROPERTY AP ON AP.ID = AR.PropertyID
    AND AP.ID IN (1672, 1673, 1674)
ORDER BY AR.ComputerID, AP.Name

Which yields the following result:

ComputerID  Name                Value
----------  ----                -----
595640      DisplayName         Windows 8.1 x64 - Mobile Device Image - v3.2 
595640      SequenceName        Windows 8.1 x64 - Mobile Device Image
595640      SequenceVersion     3.2
631459      DisplayName         Windows 8.1 x64 - Mobile Device Image - v3.2 
631459      SequenceName        Windows 8.1 x64 - Mobile Device Image
631459      SequenceVersion     3.2

In BESEXT.COMPUTER I have the following values:

ID  ComputerID  ComputerName
--  ----------  ------------
1   595640      PO121203866
2   631459      PO121201739
3   1101805     PO121201100

I want to perform a left outer join of all my computer objects on the first select, so that I know which computers I do not have a value for.

So, first I do a simple inner join on the previous selection:

SELECT
    C.ComputerName,
    R.ComputerID,
    R.Name,
    R.Value
FROM (
    SELECT
        AR.ComputerID,
        AP.Name,
        AR.Value
    FROM BESEXT.ANALYSIS_PROPERTY_RESULT AR
    JOIN BESEXT.ANALYSIS_PROPERTY AP ON AP.ID = AR.PropertyID
        AND AP.ID IN (1672, 1673, 1674)
) R
JOIN BESEXT.COMPUTER C ON C.ComputerID = R.ComputerID
ORDER BY R.ComputerID, R.Name

Which, predictably, yields the following resultset:

ComputerName    ComputerID  Name            Value
------------    ----------  ----            -----
PO121203866     595640      DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121203866     595640      SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121203866     595640      SequenceVersion 3.2
PO121201739     631459      DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121201739     631459      SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121201739     631459      SequenceVersion 3.2

Now, for the grand finale, let's do the LEFT OUTER JOIN:

SELECT
    C.ComputerName,
    R.ComputerID,
    R.Name,
    R.Value
FROM (
    SELECT
        AR.ComputerID,
        AP.Name,
        AR.Value
    FROM BESEXT.ANALYSIS_PROPERTY_RESULT AR
    JOIN BESEXT.ANALYSIS_PROPERTY AP ON AP.ID = AR.PropertyID
        AND AP.ID IN (1672, 1673, 1674)
) R
-- LEFT OUTER JOIN ADDED HERE! 
LEFT OUTER JOIN BESEXT.COMPUTER C ON C.ComputerID = R.ComputerID
ORDER BY R.ComputerID, R.Name

This yields the exact same resultset as with the inner join!

This is not what I'm looking for, and it is not at all what I was expecting. Now it's pretty late here and I'm basically doing some work out of insomnia, but I think this is about as simple and example of an Outer Join as it can get, right? The result I'm looking to achieve with the outer join is this:

ComputerName    ComputerID  Name            Value
PO121203866     595640      DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121203866     595640      SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121203866     595640      SequenceVersion 3.2
PO121201739     631459      DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121201739     631459      SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121201739     631459      SequenceVersion 3.2
PO121201100     NULL        NULL            NULL
PO121201100     NULL        NULL            NULL
PO121201100     NULL        NULL            NULL

P.S.: To be completely honest, the result I'm looking for is more like this, but I feel that would be a different question altogether:

ComputerName    Name            Value
------------    ----            -----
PO121203866     DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121203866     SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121203866     SequenceVersion 3.2
PO121201739     DisplayName     Windows 8.1 x64 - Mobile Device Image - v3.2 
PO121201739     SequenceName    Windows 8.1 x64 - Mobile Device Image
PO121201739     SequenceVersion 3.2
PO121201100     DisplayName     NULL
PO121201100     SequenceName    NULL
PO121201100     SequenceVersion NULL

@Jeroen Oh for the love of.... I knew I should have drank a coffee before posting this question. Thanks man, probably saved me a couple of hours :) — romatthe, Feb 23 '15 at 00:22
Once you can no longer tell left from right, that's probably a sign that you need more sleep, not coffee. But whatever works for you. Hope that insomnia clears up. :-) — Jeroen Mostert, Feb 23 '15 at 00:26

score 4 · Accepted Answer · answered Feb 23 '15 at 00:18

The query you're looking can be written simply as this:

SELECT ComputerName, A.ComputerID, Name, Value FROM BESEXT.COMPUTER A
CROSS JOIN (SELECT * FROM BESEXT.ANALYSIS_PROPERTY WHERE ID BETWEEN 1672 AND 1674) B
LEFT JOIN BESEXT.ANALYSIS_PROPERTY_RESULT C ON A.ComputerId = C.ComputerId AND B.ID = C.PropertyId
ORDER BY ComputerId, Name

Start by getting all of the computer-property combinations you care about:

SELECT * FROM BESEXT.COMPUTER A
CROSS JOIN (SELECT * FROM BESEXT.ANALYSIS_PROPERTY WHERE ID BETWEEN 1672 AND 1674) B

This yields the results:

ID  ComputerId  ComputerName  ID    Name
--  ----------  ------------  --    ----
1   595640      PO121203866   1672  DisplayName
2   631459      PO121201739   1672  DisplayName
3   1101805     PO121201100   1672  DisplayName
1   595640      PO121203866   1673  SequenceName
2   631459      PO121201739   1673  SequenceName
3   1101805     PO121201100   1673  SequenceName
1   595640      PO121203866   1674  SequenceVersion
2   631459      PO121201739   1674  SequenceVersion
3   1101805     PO121201100   1674  SequenceVersion

From there, you simply perform a left join on BESEXT.ANALYSIS_PROPERTY_RESULT to get your values, and you include the ORDER BY clause to sort it.

Thank you! Kudos for solving my little PS there ;) – romatthe Feb 23 '15 at 00:22 — romatthe, Feb 23 '15 at 00:22

score 1 · Answer 2 · answered Feb 23 '15 at 00:19

1

You can do this by using a cross join to set up the properties for all computers and then a left join to connect to the actual property values for those computers that have values set:

SELECT * FROM (
    SELECT
       C.ComputerName,
       C.ComputerID,
       AP.Name,
       AP.ID
    FROM BESEXT.COMPUTER C
    CROSS JOIN BESEXT.ANALYSIS_PROPERTY AP
    WHERE AP.ID IN (1672, 1673, 1674)
) AP
LEFT JOIN BESEXT.ANALYSIS_PROPERTY_RESULT AR  
ON AP.ComputerID = AR.ComputerID AND AP.ID = AR.PropertyID
ORDER BY AP.ComputerName DESC, AP.Name

answered Feb 23 '15 at 00:19

jpw

44,361
6
66
86

Sorry, had to accept Tim's answer below. Seems like he answered 1 minute before you. Still, thank you! Cross join gets me everytime :) – romatthe Feb 23 '15 at 00:24
@RobinMattheussen The answer by Tim was better anyway :) I overcomplicated mine a bit for no apparent reason (other than not working through it enough). – jpw Feb 23 '15 at 00:25

score 0 · Answer 3 · answered Feb 23 '15 at 00:19

0

As a L.O.J it'd be like this?

SELECT
    R.ComputerName,
    C.ComputerID,
    C.Name,
    C.Value
FROM BESEXT.COMPUTER R
-- LEFT OUTER JOIN ADDED HERE! 
LEFT OUTER JOIN 
(
    SELECT
        AR.ComputerID,
        AP.Name,
        AR.Value
    FROM BESEXT.ANALYSIS_PROPERTY_RESULT AR
    JOIN BESEXT.ANALYSIS_PROPERTY AP ON AP.ID = AR.PropertyID
        AND AP.ID IN (1672, 1673, 1674)
) C 
  ON C.ComputerID = R.ComputerID
ORDER BY C.ComputerID, C.Name

answered Feb 23 '15 at 00:19

whytheq

34,466
65
172
267

1

Yes, that does it. Maybe I should try to get some sleep. Looks like I can't think straight anymore. – romatthe Feb 23 '15 at 00:24

Left outer join in SQL Server 2012 LocalDB not working?

3 Answers3