Search code examples
sqlsql-server-2012select-query

from [table] when [new column is notnull]


I have written a query for select some results from different tables with a union all. After the from you can use the where. In that where I want to use a column that I 'made' in the query. How can I do this?

example

 SELECT    
    [column1] AS 'K1', 
    ISNULL([column2], 'else')  + ' ' + ISNULL([column3], 'else') AS 'K2'    
 FROM 
    [table]  
 FULL JOIN
    [table] ON A.Name = C.AccountIdName 
 WHERE
    A.Name IS NOT NULL  
    AND K1 IS NOT NULL

Is that possible to take the K1 and set in into the where? Or is there an other option?


UPDATE: New example

SELECT    
        [column1] AS 'K1', 
        ISNULL([column2], 'else')  + ' ' + ISNULL([column3], 'else') AS 'K2'    
     FROM 
        [table]  
     FULL JOIN
        [table] ON A.Name = C.AccountIdName 
     WHERE
        A.Name IS NOT NULL  
        **AND K1 IS NOT NULL
        AND K3 IS NOT NULL**

UNION ALL

SELECT    
        [column1] AS 'K1', 
        ISNULL([column2], 'else')  + ' ' + ISNULL([column3], 'else') AS 'K2'    
     FROM 
        [table]  
     FULL JOIN
        [table] ON A.Name = C.AccountIdName 
     WHERE
        A.Name IS NOT NULL  
        **AND K2 IS NOT NULL 
        AND K4 IS NOT NULL**

As you can see I want in the first select K1 en K3 and in the second I want K2 end K4

I hope you can solve this problem too, this was a good solution.

SELECT * FROM 
(
 SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2' 
 FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
) 
res WHERE res.K1 IS NOT NULL

Solution

  • You can use it, but as a result of your subquery

    SELECT * FROM 
    (
     SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2' 
     FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
    ) 
    res WHERE res.K1 IS NOT NULL
    

    UPDATED

    SELECT * FROM 
    (
       SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2' 
       FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
    ) 
    res1 WHERE res1.K1 IS NOT NULL AND res1.K3 IS NOT NULL
    UNION ALL
    SELECT * FROM 
    (
       SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2' 
       FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
    ) 
    res2 WHERE res2.K2 IS NOT NULL AND res2.K4 IS NOT NULL //BUT DOES IT HAS K4???