asked
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. Thanks
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
I'm a bit confused as to why this wouldn't work: DECLARE @estable TABLE ( QuartKey INTEGER, Revenue INTEGER, ManagerID INTEGER, CustomerID INTEGER ); INSERT INTO @estable VALUES (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); SELECT DISTINCT e.CustomerID FROM @estable AS e WHERE ( QuartKey = 20174 AND Revenue = 0 ) OR ( 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 WHERE (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) ) OR (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.
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!
The second query (the one with all the subqueries) will work for that.
Have you tried select * from table1 where id != 2
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
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)
ok so that becomes (year = 2017 and revenue > 0) OR (year = 2018 and revenue =0)
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.
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.
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
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?
17 People are following this question.
