Results 1 to 3 of 3

Thread: best way to select data

  1. #1
    Join Date
    Jul 2003
    Posts
    421

    best way to select data

    In my table A I have columns like
    UniversityN, AstComposition, BstComposition, AstHall, BstHall, ContinueFee, DepositComposition all the value either is 'on' or 'Null'
    if I want to select those data the have value in AstComposition, BstComposition, ContinueFee, DepositComposition or in AstHall, BstHall
    and group by University Number , is a better way to do it ??

    meantime I use
    Code:
    select * from table A
    where and AstComposition='on'
    
    union all
    select * from table A
    and AstComposition !='on' and BstComposition='on'
    union all
    select * from table A
    and AstComposition !='on' and BstComposition!='on' and ContinueFee ='on'
    union all
    select * from table A
    and AstComposition !='on' and BstComposition!='on' and ContinueFee !='on' and DepositComposition='on'
    is that a better way to do the select ???
    ________
    Vapor Genie
    Last edited by sql; 03-06-2011 at 02:16 AM.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    sql, What database are you working with? I am not sure I follow exactly what you want but here is something that might work:
    Code:
    SELECT *
    FROM table a
    WHERE COALESCE(Astcomposition, Bstcomposition, ContinueFee, DepositComposition) = 'on'
    The COALESCE function returns the first value that is NOT NULL. If all values are NULL the result is NULL. You can think of it as a nested IF statement. IF first value is NOT NULL return it ELSEIF the second valuse is NOT NULL return it ELSEIF...

    Since you indicated the values will be only NULL or ON, this should get you the same results as you multi-UNION query.

  3. #3
    Join Date
    Jul 2003
    Posts
    421
    Thank you!
    ________
    Dodge Magnum History
    Last edited by sql; 03-06-2011 at 02:16 AM.

Posting Permissions

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