Adding multiple select statements to a table in SQL

Question

I have 2 separate select statements and I wanted to add them as 2 separate columns in the results table in SQL so I can compare them. They're both being selected from the same table so I just wanted to know how to go about it.

select distinct(varA) from table1 
        where cond = '0.0000' 

select distinct(varA) from table1 
        where cond > '0.0000' 
order by varA

Answer 1

So if your varA value is "A", why would you want two columns with A and A? Wouldn't you rather compare the actual "cond" values? Regardless, both solutions below:

Create table and insert values:

    create table table1 (
      varA varchar(10), 
      cond numeric
    );

    insert into table1 values 
    ('a', 0.0000), 
    ('b', 0.0000), 
    ('c', 0.02), 
    ('a', 0.4), 
    ('c', 0.0000);

Query if wanting to return just the varA values, which is completely unhelpful in my opinion. If your DBMS does not support Common Table Expression, then change that to a join.

    with cte1 as (
      select distinct varA
      from table1
      where cond = 0.0000
    )
    select distinct c.varA as zero_var, 
    t.varA as over_zero_var
    from table1 t
    join cte1 c
      on t.varA = c.varA
    where t.cond > 0.0000
    order by 1,2

Results, again which are seemingly unhelpful.

    zero_var    over_zero_var
    a           a
    c           c

Query if returning the COND values, which makes much more sense, and assume this is what you actually want.

    with cte1 as (
      select distinct varA, cond
      from table1
      where cond = 0.0000
    )
    select distinct c.varA as varA, 
    c.cond, t.cond
    from table1 t
    join cte1 c
      on t.varA = c.varA
    where t.cond > 0.0000
    order by 1,2

Results:

    vara    cond    cond
    a       0.0000  0.4
    c       0.0000  0.02