- Return all properties if filter table is empty. If filter table has any data then return properties that matches to filter.
This is how we can create such query:
CREATE TABLE MyData(Id INT, Something VARCHAR(10), OwnerId INT); CREATE TABLE OwnerFilter(OwnerId INT); INSERT INTO MyData VALUES(1, 'AAAA', 1); INSERT INTO MyData VALUES(2, 'BBBB', 1); INSERT INTO MyData VALUES(3, 'CCCC', 1); INSERT INTO MyData VALUES(4, 'AASS', 2); -- Other table is empty. Query returns all rows. SELECT * FROM (SELECT NULL AS Gr) AS Dummy LEFT JOIN OwnerFilter F ON (1 = 1) JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId); -- Other table has data. Query returns only matched rows. INSERT INTO OwnerFilter VALUES(2); SELECT * FROM (SELECT NULL AS Gr) AS Dummy LEFT JOIN OwnerFilter F ON (1 = 1) JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);
Here you can try it on fiddler: http://sqlfiddle.com/#!6/0f9d9/7