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
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.
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)