Results 1 to 15 of 15

Thread: [RESOLVED] SQL - Select two columns where not null - return one row

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] SQL - Select two columns where not null - return one row

    Hi
    I cannot fix this:

    Name:  rolesNull.jpg
Views: 257
Size:  12.1 KB

    and I need this:

    Name:  rolesNull2.jpg
Views: 206
Size:  12.3 KB

    My query:

    Code:
    SELECT Items As UserName, 
                                     (case when AdditionalInfo = 'ICRelated' then ListItemName else NULL end) as ICroles,
                                     (case when AdditionalInfo Is Null then ListItemName else NULL end) as DVRoles 
                                    FROM DV_Arena_ListItems 
                                     WHERE  
                                    ListItemType = 'Assigned Role' And
                                    Items = 'MIAD' And
                                    ActiveStatus = 'Yes'
                                    Group by items, AdditionalInfo, ListItemName
    thank you so much for help; I really don't know how to proceed.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL - Select two columns where not null - return one row

    Is this specific to those two rows or do you want to be able to handle a more general case? I would think that you would just be able to group on the first column and get the max of the other two columns. I would expect that to get the text value from each column and exclude the NULL.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Select two columns where not null - return one row

    It is a more general case where the values of those two columns are different than the ones we see here. Also in some cases there might be only one row to begin with
    If it is very complicated, I could get this to a datatable and work it out on client side.

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL - Select two columns where not null - return one row

    1. Remove AdditionalInfo and ListItemName from Group by items, AdditionalInfo, ListItemName so it become Group by items only.

    2. Put MAX aggregate function in front of both ICroles and DVRoles expressions so these become MAX(case when ... end)

    cheers,
    </wqw>

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL - Select two columns where not null - return one row

    Very simple when you look at it from a 30,000 foot view...

    First of all - NULL is evil and hard to work with - I changed your query to have '' instead of null - see below:

    Code:
    SELECT Items As UserName, (case when AdditionalInfo = 'ICRelated' then ListItemName else '' end) as ICroles,
                                     (case when AdditionalInfo Is Null then ListItemName else '' end) as DVRoles 
                                    FROM DV_Arena_ListItems 
                                     WHERE  
                                    ListItemType = 'Assigned Role' And
                                    Items = 'MIAD' And
                                    ActiveStatus = 'Yes'
                                    Group by items, AdditionalInfo, ListItemName
    Now here is the simple part.

    What is the "set" you are working with? Answer - two rows.

    What do you want from that "group" of two rows?

    You want the value that is not blank - that would be the MAX() function that handles that!

    Code:
    SELECT Items As UserName, Max(case when AdditionalInfo = 'ICRelated' then ListItemName else '' end) as ICroles,
                                     Max(case when AdditionalInfo Is Null then ListItemName else '' end) as DVRoles 
                                    FROM DV_Arena_ListItems 
                                     WHERE  
                                    ListItemType = 'Assigned Role' And
                                    Items = 'MIAD' And
                                    ActiveStatus = 'Yes'
                                    Group by items

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by wqweto View Post
    1. Remove AdditionalInfo and ListItemName from Group by items, AdditionalInfo, ListItemName so it become Group by items only.

    2. Put MAX aggregate function in front of both ICroles and DVRoles expressions so these become MAX(case when ... end)

    cheers,
    </wqw>
    Beat me to it by 2 minutes - but you shouldn't max that null...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by szlamany View Post
    . . . but you shouldn't max that null...
    LOL! The warning is going to scare me? :-))

    Btw, the bigger problem is with AVG, not MAX.

    AVG on 1, 2, 3, NULL, 5 takes the sum and divides by 4 (ignoring the NULL) instead of 5 as some might expect.

    cheers,
    </wqw>

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by wqweto View Post
    LOL! The warning is going to scare me? :-))

    Btw, the bigger problem is with AVG, not MAX.

    AVG on 1, 2, 3, NULL, 5 takes the sum and divides by 4 (ignoring the NULL) instead of 5 as some might expect.

    cheers,
    </wqw>
    As I warned - NULL is evil and you don't want it in your AGGREGATE functions! Unexpected behavior especially to the novice.

    I find that by default nature I always wrap column names in ISNULL() for all comparisons.

    Code:
    Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End
    And always use CASE ...ELSE... so that your CASE statements are always DETERMINISTIC!

    "always DETERMINISTIC" - is that being redundant?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Select two columns where not null - return one row

    That is fantastic. Thank you all so much; also for the explanation.

  10. #10
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL - Select two columns where not null - return one row

    Btw, in Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End the use of IsNull is completely redundant. The result of the whole expression cannot be NULL no matter the value (or lack of value) in SomeYNField column.

    cheers,
    </wqw>

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by wqweto View Post
    Btw, in Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End the use of IsNull is completely redundant. The result of the whole expression cannot be NULL no matter the value (or lack of value) in SomeYNField column.

    cheers,
    </wqw>
    On the surface what you say is obviously true, but for maintainability and furture enhancements - someone could change that to <> instead of = and then you have big problems.

    I always alias my SELECT's with 2 character "common" to that database aliases, even if only one TABLE. Adding JOINS to poorly alias'ed SELECT's is a nightmare

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by Grand View Post
    It is a more general case where the values of those two columns are different than the ones we see here. Also in some cases there might be only one row to begin with
    If it is very complicated, I could get this to a datatable and work it out on client side.
    In future, explain what you actually want. Providing an example can help but specifying a single case without specifying the general case is a great way to waste everyone's time. There are going to be a number of ways to achieve a result in a specific case that won't work in the general case. ALWAYS describe the rules in their entirety, because that's what the code has to implement.

  13. #13
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by szlamany View Post
    On the surface what you say is obviously true, but for maintainability and furture enhancements - someone could change that to <> instead of = and then you have big problems.
    Sure, the same way someone might swap 'Yes' and 'No' and get themselves into trouble land.

    Quote Originally Posted by szlamany View Post
    I always alias my SELECT's with 2 character "common" to that database aliases, even if only one TABLE. Adding JOINS to poorly alias'ed SELECT's is a nightmare
    Nice! We usually use 3 letter aliases with a separate 't' for temp tables i.e. lnk vs tlnk -- the second comes from a temp table.

    We use tabsize=4 but use 3 tabs for nested level indentation and format SELECTs with comma-first on new line so that commenting out columns is easy.

    We format our T-SQL consistently like every other "application" code (VB6 or C#) and never leave messy unformatted chunks. We have a lot (100s of thousands of LOC) of T-SQL code under source control like this.

    cheers,
    </wqw>

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL - Select two columns where not null - return one row

    Sample Data taken from here: https://www.vbforums.com/showthread....onditions-true

    tested in SQLite
    Code:
    SELECT DISTINCT T1.Items, T2.ICRoles, T3.DVRoles  
    FROM test AS T1 
    INNER JOIN 
    (SELECT DISTINCT Items, ItemName AS ICRoles FROM test WHERE Info='ICRelated') As T2 
    ON 
    T2.Items=T1.Items 
    INNER JOIN 
    (SELECT DISTINCT Items, ItemName AS DVRoles FROM test WHERE Info Is Null) AS T3 
    ON 
    T3.Items=T1.Items 
    WHERE 
    T1.Items='A'
    Returns:
    Items ICRoles DVRoles
    A Observer Admin
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL - Select two columns where not null - return one row

    Quote Originally Posted by wqweto View Post
    We format our T-SQL consistently like every other "application" code (VB6 or C#) and never leave messy unformatted chunks. We have a lot (100s of thousands of LOC) of T-SQL code under source control like this.
    Very nice - the only way to do it, I agree!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width