locked
Selecting and Null values RRS feed

  • Question

  • Hi all,

    I've got what must be a relatively simple query but I just can't seem to get my head around it :s

    In the project I am working on I have a web interface which displays a list of parameters and their values, not every parameter has a value and in these cases we use a default value which are stored in a separate table.  What I'm wanting to do is where the parameter value is null in the first table I want to instead display the corresponding default value from the second table.  Would anyone be able to advise me on the best way to go about this?

    Thanks in advance for your help


    Kristin
    Tuesday, March 9, 2010 2:24 PM

Answers

  • I see, then it's very simple and we already gave you the solution.

    Select T1.ID, T1.[Parameter Name], ISNULL(T1.[Parameter Value],T2.[Default Value]) as [Parameter Value]
    from Table1 T1 inner join Table2 T2 on T1.ID = T2.ID
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by klacy1981 Tuesday, March 9, 2010 3:37 PM
    Tuesday, March 9, 2010 3:20 PM

All replies

  • The question is not entirely clear. Do you want to somehow query the default values of the second table?

    Or you want something like

    select ISNULL(@MyParameter, mySecondTable.SomeField) as ParamValue, ...
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, March 9, 2010 2:27 PM
  • Hello ,
    You can modify the code according to the requirement, just an idea how you can achieve this!! Good luck
    use tempdb
    go
    Create table #T(id int, name varchar(20))
    go
    insert into #T values(1,'Test')
    go
    insert into #T values(2,'NotTest')
    go
    
    Select * from #T
    
    Create table #T1(id int , name varchar(20))
    go
    insert into #T1 values(1,'test')
    
    
    Create procedure #myproc( @id int)
    as
    begin
    Select * from #T
    where id=ISNULL(@id,(Select id from #T1))  -- if id is null, then select the default value from second table,you can define your critera for default
    end
    
    Exec #myproc Null
    --Result
    --1	Test
    
    Exec #myproc 2
    --Results
    --2	NotTest
    
    Tuesday, March 9, 2010 2:36 PM
  • Sorry not very good at explaing what I want. I want to query the first table for any null values when the stored procedure is called and for any null value it finds then look at the 2nd table for the corresponding default value.
    Tuesday, March 9, 2010 2:37 PM
  • Can you post your tables creation script, some input and desired output? I'm still not clear by what do you mean 'default value' and how does table 2 correspond to table 1.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, March 9, 2010 2:40 PM
  • Something like this?

    DECLARE @t TABLE(ID INT,Value1 VARCHAR(20),Value2 VARCHAR(20))
    INSERT INTO @t
    SELECT 1,'Test1',NULL UNION ALL SELECT 2,NULL,'Test2'
    
    DECLARE @u TABLE(ID INT,Data1 VARCHAR(20),Date2 VARCHAR(20))
    INSERT INTO @u
    SELECT 1,'Data1',NULL UNION ALL SELECT 2,'Data2',NULL
    
    SELECT t.ID,COALESCE(Value1,Data1) AS Value
    FROM @t t
    	JOIN @u u 
    		ON t.ID = u.ID

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Naomi N Tuesday, March 9, 2010 3:21 PM
    Tuesday, March 9, 2010 2:42 PM
  • Table 1 consists of the following columns - ID, Parameter Name, Parameter Value

    Table 2 consists of - ID,  Default Value etc.... - the data in this table contains extra information about the Parameters in table 1 i.e. its default value.  So the tables would have to be joined as part of the query.

    When Parameter Value in Table 1 is null I want the Default Value from Table 2 to be displayed.

    Tuesday, March 9, 2010 3:15 PM
  • I see, then it's very simple and we already gave you the solution.

    Select T1.ID, T1.[Parameter Name], ISNULL(T1.[Parameter Value],T2.[Default Value]) as [Parameter Value]
    from Table1 T1 inner join Table2 T2 on T1.ID = T2.ID
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by klacy1981 Tuesday, March 9, 2010 3:37 PM
    Tuesday, March 9, 2010 3:20 PM
  • I see, then it's very simple and we already gave you the solution.

    Select T1.ID, T1.[Parameter Name], ISNULL(T1.[Parameter Value],T2.[Default Value]) as [Parameter Value]
    from Table1 T1 inner join Table2 T2 on T1.ID = T2.ID
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Thank you for your help - I'm relatively new to this so apologies for being slow on the uptake!!!

    Thanks once again

    Kristin
    Tuesday, March 9, 2010 3:38 PM
  • It didn't seem to work however the code above yours did work.  Thanks for your help anyway
    Tuesday, March 9, 2010 3:48 PM