CODEWITHSUNDEEP
sqlsql-server
joelc
joelc

Reputation: 2761

SQL SELECT from one of two columns where column value is not equal to something

if I have a table with two columns, and the value for one of them will be known, is there a way to SELECT the value that is not equal to the known value and cast it as another column name?

For instance

columna       columnb
1             5
3             1
4             1
1             7

I want to query both columns in the table above for all values not equal to 1, and return the list in a single column called column (or similar), i.e. the resultant table should be:

column
3
4
5
7

Upvotes: 0

Views: 2425

Answers (2)

dani herrera
dani herrera

Reputation: 51665

You are looking for:

Your query:

SELECT 
       columnb as [column]  --here the alias
FROM 
       yourTable
WHERE 
       columnb <> 1 or columnea <>1   --here the where clause

Notice: you can use and or or operator in where clause.

Quoting t-sql select docs:

column_ alias Is an alternative name to replace the column name in the query result set. For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity. Aliases are used also to specify names for the results of expressions, for example:

SELECT AVG(UnitPrice) AS [Average Price] FROM Sales.SalesOrderDetail;

Edited due to OP comments:

To get values from both columns, the easiest way for you is a UNION:

SELECT 
       columnb as [column]  --here the alias
FROM 
       yourTable
WHERE 
       columnb <> 1         --here the where clause
UNION ALL
SELECT 
       columna as [column]  --here the alias
FROM 
       yourTable
WHERE 
       columna <> 1         --here the where clause

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think you just want:

select distinct col
from t cross join
     (values (columna), (columnb)) v(col)
where col <> 1;

This will capture situations where both columns are not "1".

If your intention is something along the lines of "the other talker" in a chat, then:

select t.*, (case when columna <> 1 then columna else columnb end) as col
from t
where 1 in (columna, columnb);

Upvotes: 1

Related Questions