CODEWITHSUNDEEP
sqlsql-servert-sqljoin
William YK
William YK

Reputation: 1125

How to write a query so that it only returns results where two columns are zero?

I have a single table that I need to join to itself, using a similar style to what I have below, even if there are other ways to solve.

I'm trying to exclude only rows that have both Leftover and Remainder equal to 0.

My problem is rows C and F keep getting excluded.

All rows:

+--------+----------+-----------+
| Letter | Leftover | Remainder |
+--------+----------+-----------+
| A      |        1 |         2 |
| B      |        2 |         3 |
| C      |        3 |         0 |
| D      |        0 |         0 |
| E      |        0 |         0 |
| F      |        0 |         4 |
+--------+----------+-----------+

Desired output:

+--------+----------+-----------+
| Letter | Leftover | Remainder |
+--------+----------+-----------+
| A      |        1 |         2 |
| B      |        2 |         3 |
| C      |        3 |         0 |
| F      |        0 |         4 |
+--------+----------+-----------+

Actual output:

+--------+----------+-----------+
| Letter | Leftover | Remainder |
+--------+----------+-----------+
| A      |        1 |         2 |
| B      |        2 |         3 |
+--------+----------+-----------+

Here's my sample code:

create table #Temp
(
    Letter char,
    Leftover int,
    Remainder int
)


insert into #Temp values ('A', 1, 2 )
insert into #Temp values ('B', 2, 3 )
insert into #Temp values ('C', 3, 0 )
insert into #Temp values ('D', 0, 0)
insert into #Temp values ('E', 0, 0)
insert into #Temp values ('F', 0, 4)

select * from #Temp

select a.* from #Temp a
    join #Temp b on a.Letter = b.Letter
    where (b.Remainder != 0 AND b.Leftover != 0)



If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End

What am I missing??

Upvotes: 0

Views: 228

Answers (3)

Rajat
Rajat

Reputation: 5803

How about this?

select * from #temp
where leftover+remainder>0

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

You don't need the join. Try

SELECT
    *
FROM
    #Temp AS T
WHERE
    T.Leftover = 0 AND T.Remainder = 0

This will give you rows D and E.

Alternatively NOT (T.Leftover = 0 AND T.Remainder = 0) will give you rows A, B, C and F.

What the join is saying is exclude rows where are least one of the items is zero.

For completeness:

SELECT
    a.*
FROM
    #Temp AS a
    JOIN 
    #Temp b on a.Letter = b.Letter
WHERE
    NOT (b.Leftover = 0 AND b.Remainder = 0)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You want or, not and:

where b.Remainder <> 0 or b.Leftover <> 0

Or, you can phrase this as:

where not (b.Remainder = 0 and b.Leftover = 0)

These are logically equivalent.

Upvotes: 1

Related Questions