Interesting LEFT JOIN query question.

Question

Well interesting to me at least...

Say I have two tables:

myLookUpTable:

lookupId  | Name
--------    -----
1           Red
2           Green
3           Blue

and InfoTable:

infoId  lookupId  Amount  ParentId
------  --------  ------  --------
1       1         2       332
2       3         14      332

How would I write a query that returns every row in myLookUpTable and includes associated information from InfoTable if it exists for a certain ParentId?

Example:

querying for parentId 221 would return The following for Name and Amount:

Name  Amount
----  ------
Red   
Green
Blue  

and querying for parentId 332 would return The following for Name and Amount:

Name  Amount
----  ------
Red   2
Green 
Blue  14

I've tried about ten variations of left joins with no luck. Below is my latest:

SELECT mlut.Name, it.Amount
FROM   myLookUpTable as mlut
LEFT JOIN InfoTable as it
ON     mlut.lookupId = it.lookUpId OR it.ParentId is null
where  it.ParentId = 332

This seems like a simple issue, am I just over looking something?

Answer 1

I think this will do what you want.

SELECT mlut.Name, it.Amount 
  FROM myLookUpTable as mlut 
  LEFT JOIN InfoTable as it 
    ON mlut.lookupId = it.lookUpId 
   AND it.ParentId = 332 

SQL Server 2005 Testing results below

CREATE TABLE [dbo].[myLookUpTable](
    [lookupId] [int] NOT NULL,
    [Name] [varchar](10) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[InfoTable](
    [infoId] [int] NOT NULL,
    [lookupId] [int] NOT NULL,
    [Amount] [int] NOT NULL,
    [ParentId] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO myLookUpTable Values (1,'Red')
INSERT INTO myLookUpTable Values (2,'Green')
INSERT INTO myLookUpTable Values (3,'Blue')

INSERT INTO infoTable Values (1,1,2,332)
INSERT INTO infoTable Values (2,3,14,332)

SELECT mlut.Name, it.Amount  
  FROM myLookUpTable as mlut  
  LEFT JOIN InfoTable as it  
    ON mlut.lookupId = it.lookUpId  
   AND it.ParentId = 221

Red   NULL
Green NULL
Blue  NULL

SELECT mlut.Name, it.Amount  
  FROM myLookUpTable as mlut  
  LEFT JOIN InfoTable as it  
    ON mlut.lookupId = it.lookUpId  
   AND it.ParentId = 332

Red   2
Green NULL
Blue  14

Answer 2

The OR it.ParentId is null part shouldn't be there.

Otherwise - looks good. If you have some particular issues with it, please describe them.

Answer 3

The problem is this line:

where  it.ParentId = 332

when you use this where condition, you are eliminating any rows that don't have a ParentId, such as rows from the table on the left where there are no joining rows on the right. Use:

where it.ParentId IS NULL or it.ParentId = 332

get rid of the IS NULL check from the join statement.

Answer 4

Instead of

where  it.ParentId = 332

you need to do

AND it.ParentId = 332

Also get rid of OR it.ParentId is null

See also here http://stackoverflow.com/questions/3462514/mysql-how-to-get-null-if-equality-does-not-exist/3462532#3462532

Answer 5

The WHERE clause for ParentID is undo-ing your efforts to LEFT JOIN all the null lookup values. You can move that constraint into the LEFT JOIN criteria:

SELECT mlut.Name, it.Amount
FROM   myLookUpTable as mlut
LEFT JOIN InfoTable as it
ON     mlut.lookupId = it.lookUpId AND it.ParentId = 332

Answer 6

The post from Dave Barker is the right answer BUT: If you always need to return exact number of rows that matches the number of rows in myLookUpTable, and the (lookupid,ParentId) is not unique in the InfoTable you might want to use one of the following options:

-- sample contents - note that there are multiple amounts for lookupid, parentid (3,332)
INSERT INTO infoTable Values (1,1,2,332)
INSERT INTO infoTable Values (2,3,14,332)
INSERT INTO infoTable Values (3,3,24,332)
INSERT INTO infoTable Values (4,3,34,0)
INSERT INTO infoTable Values (5,3,44,332)

 -- option 1
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN 
        (
            SELECT lookUpId, SUM(Amount) as Amount
            FROM InfoTable
            WHERE ParentId = 332
            GROUP BY lookupId
        ) as it  
        ON mlut.lookupId = it.lookUpId  

    -- option 2
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN 
        (
            SELECT lookUpId, ParentId, SUM(Amount) as Amount
            FROM InfoTable
            GROUP BY lookupId, ParentId 
        ) as it  
        ON mlut.lookupId = it.lookUpId  
            AND it.ParentId = 332

    -- option 2 using CTE
    ;WITH it AS (
        SELECT lookUpId, ParentId, SUM(Amount) as Amount
        FROM InfoTable
        GROUP BY lookupId, ParentId 
    )
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN it 
        ON mlut.lookupId = it.lookUpId  
        AND it.ParentId = 332

/*
Name       Amount
---------- -----------
Red        2
Green      NULL
Blue       82
*/