Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove NULL values from multiple LEFT JOIN in sql server

I have the following tables

ITEM1

ID | NAME | GEARS | ITEM2_ID  |
-------------------------------
1  | Test | 56    | 4         |
2  | Test2| 12    | 2         |

ITEM3

ID | NAME | DATA  | ITEM2_ID  |
-------------------------------
1  | Test | 1     | 1         |
2  | Test7| 22    | 3         |

ITEM2

ID |   VALUE       |
--------------------
1  |   is simple   |  
2  |   is hard     | 
3  |   is different|
4  |   is good     |
5  |   very good   |

And my query

SELECT TOP(3) * FROM (
    SELECT ID, 
       rankTable.RANK as RANK_,
        TOTALROWS = COUNT(*) OVER() 
 FROM ITEM2
 INNER JOIN 
     CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
 ON ITEM2.ID = rankTable.[KEY]
) as ITEM2table

LEFT JOIN (
    SELECT ID, 
      NAME, 
      GEARS, 
      ITEM2_ID 
    FROM ITEM1
) as ITEM1table
ON ITEM1table.ITEM2_ID = ITEM2table.ID

LEFT JOIN (
    SELECT ID, 
        NAME, 
        DATA, 
        ITEM2_ID 
    FROM ITEM3
) as ITEM3table
ON ITEM3table.ITEM2_ID = ITEM2table.ID

and the results

enter image description here

How to remove (if is possible) the first row (ID = 5) using the above SQL query ? Also I want to show TOTALROWS = 1 because other row contains NULL's except first 3 columns.

Thank you.

like image 977
Snake Eyes Avatar asked May 14 '12 06:05

Snake Eyes


People also ask

Does LEFT join ignore NULL?

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

What function would you use to remove NULL values when using a left join?

You can use ISNULL(MAX(T2. LOGIN_TIME), 'Default Value') to replace the NULL . But first you should consider that LOGIN_TIME varchar(255) with values such as 17.07.

How do you handle NULL in joins?

The result of a join of null with any other value is null. Because null values represent unknown or inapplicable values, Transact-SQL has no basis to match one unknown value to another. You can detect the presence of null values in a column from one of the tables being joined only by using an outer join.

IS NULL with left join?

The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.


2 Answers

If I understand correctly, you want to keep only the rows where either the first or the second (or both) outer join succeeds:

WHERE ITEM1table.ITEM2_ID IS NOT NULL 
   OR ITEM3table.ITEM2_ID IS NOT NULL 

Some simplifications can be done on the query. No need for the nested subqueries:

SELECT TOP(3) 
    ITEM2table.ID, 
    rankTable.RANK as RANK_,
    TOTALROWS = COUNT(*) OVER(),
    ITEM1table.*,
    ITEM3table.*  

FROM 
        ITEM2 

    INNER JOIN 
        CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
    ON ITEM2.ID = rankTable.[KEY]

    LEFT JOIN 
        ITEM1  as ITEM1table
    ON ITEM1table.ITEM2_ID = ITEM2.ID

    LEFT JOIN 
        ITEM3  as ITEM3table
    ON ITEM3table.ITEM2_ID = ITEM2.ID

WHERE ITEM1table.ITEM2_ID IS NOT NULL 
   OR ITEM3table.ITEM2_ID IS NOT NULL 

ORDER BY  something                     --- you need to order by something
                                        --- if you use TOP. Unless you want 
                                        --- 3 (random) rows.
like image 74
ypercubeᵀᴹ Avatar answered Sep 17 '22 22:09

ypercubeᵀᴹ


Maybe there's an obvious reason, but if you want to eliminate rows where the second table doesn't have a match, why are you using a left join? It seems like your first join should be an inner join and your second should be left - that would give you the results you want in this case.

like image 41
SqlRyan Avatar answered Sep 21 '22 22:09

SqlRyan