Return all rows when condition is not met

Hi Guys

I'll try to explain the conundrum with an example. Suppose we have a table called table1, It has a single column id which has integer values. Say row values are 1,3,4,5

Now if I do select * from table1 where id = 2 I want to get all the rows since 2 does not exist in the table.


more ▼

asked Mar 16 at 02:11 PM in Default

avatar image

2k 4 12 16

what version of SQL Server are you using? Just wondering if the EXCEPT and/or INTERSECT operators might be of use to you. See https://msdn.microsoft.com/en-us/library/ms188055.aspx

Mar 21 at 03:02 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I'm a bit confused as to why this wouldn't work:

     QuartKey INTEGER, Revenue INTEGER, ManagerID INTEGER, CustomerID INTEGER
 INSERT INTO @estable
 (20174, 1800, 121, 1221),
 (20182, 1800, 131, 1331),
 (20182, 1900, 101, 1001),
 (20174, 0, 141, 1441),
 (20181, 100, 141, 1441),
 (20181, 100, 151, 1551),
 -- adding these two lines breaks the first SELECT option, but the second still works.
 (20174, 100, 161, 1661),
 (20181, 100, 161, 1661);
 FROM @estable AS e
     QuartKey = 20174 AND Revenue = 0
     QuartKey = 20181 AND Revenue > 0

However, if that's definitely not the right answer, then perhaps thinking about what you're doing in terms of subqueries might be easier:

 SELECT DISTINCT e.customerid FROM @estable AS e
 (e.customerid IN (SELECT e2.customerid FROM @estable e2 WHERE e2.QuartKey = 20174 AND e2.Revenue = 0)
     AND e.customerid IN (SELECT e3.customerid FROM @estable AS e3 WHERE e3.quartkey = 20181 AND e3.revenue > 0)
 (e.customerID NOT IN (SELECT e4.customerID FROM @estable e4 WHERE e4.QuartKey = 20174)
     AND e.customerID IN (SELECT e5.customerid FROM @estable e5 WHERE e5.quartkey = 20181 AND e5.revenue > 0)

But this is beginning to give me a headache.

more ▼

answered Mar 21 at 03:10 PM

avatar image

ThomasRushton ♦♦
42k 20 51 53

20174 100 161 1661 20181 100 161 1661

we don't want 1661 to be returned as the first condition is not met!

In a nutshell, question is about how can we treat a condition for which there is no data as true!

Mar 21 at 03:53 PM erlokeshsharma08

The second query (the one with all the subqueries) will work for that.

Mar 21 at 04:14 PM ThomasRushton ♦♦

Cool . Ill try that and let you know

Mar 21 at 04:40 PM erlokeshsharma08
(comments are locked)
10|1200 characters needed characters left

Have you tried

 select * from table1 where id != 2
more ▼

answered Mar 17 at 02:40 PM

avatar image

5.2k 66 69 77

Thanks for replying. Let me explain it in a better way. Suppose we have a where condition

id = 2 and id =3. Now for above result set it would not return anything as the first condition is not met. What I want it to do it is to return the id= 3 even because id =2 does not exist and should not be used as a comparing condition.

Thanks Lokesh

Mar 20 at 01:45 PM erlokeshsharma08
(comments are locked)
10|1200 characters needed characters left

If you want to list many values in the where clause, and still return data even when one of those does not match, you need an OR clause

 select * from table1
 where id =2 or id =3

This can also be written with an IN clause

 select * from @table1
 where id in(2,3)

more ▼

answered Mar 21 at 08:16 AM

avatar image

Kev Riley ♦♦
66.2k 48 63 81

Thanks Kev, the actual conditions are little different. It is something like

(year = 2017 and revenue > 0) and (year = 2018 and revenue =0)

so we dont have an entry for year 2017, we basically want the first condition to return true.

Mar 21 at 08:24 AM erlokeshsharma08

ok so that becomes (year = 2017 and revenue > 0) OR (year = 2018 and revenue =0)

Mar 21 at 09:12 AM Kev Riley ♦♦

OR will not serve the purpose. We want both the conditions to hold true. 2017 is just an example . It could be any value which may or may not exist inside the table

Mar 21 at 09:56 AM erlokeshsharma08

I don't understand - if the value "may or may not exist inside the table" how can the condition hold true? I think you need to give some more detailed example data.

Mar 21 at 10:12 AM Kev Riley ♦♦

Sure. Table consists of quartkey and revenue column (amongst all other columns such as managerid, customerid)

So data could be like

 20174    1800   121  1221  
 20182    1800   131  1331  
 20182    1900   101  1001  

So for every accountmanager and customer combination there may or may not an entry inside the table for quarter 20174.

And the code snippet where the conditions are getting used is something like :

customerid in (select customerid from table where

(QUARTER_KEY = 20174 AND revenue = 0) and (QUARTER_KEY = 20181 AND revenue > 0) )

I think this can be a legit scenario where you want to use AND with certain conditions and some of the conditions does not have the underlining data. Isnt it?

Mar 21 at 10:28 AM erlokeshsharma08
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 16 at 02:11 PM

Seen: 58 times

Last Updated: Mar 21 at 04:40 PM

Copyright 2017 Redgate Software. Privacy Policy