March 25, 2015 at 10:51 am
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
ELSE
County.CountyId
END
FROM
County
LEFT JOIN
SearchCriteria ON
County.CountyId = SearchCriteria.CountyId
AND
SearchCriteria.SearchId = @SearchId
)
This works; it just seems cludgy. Is there a more elegant way to do this?
March 25, 2015 at 11:02 am
If I understand the query correctly, I think this will do it:
DECLARE @SearchId INT = 100
SELECT Customer.*
FROM Customer
WHERE
NOT EXISTS(
SELECT 1
FROM SearchCriteria
WHERE
SearchCriteria.SearchId = @SearchId AND
SearchCriteria.CountyId <> Customer.CountyId
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2015 at 11:18 am
Yep, that did it. Thanks!
March 25, 2015 at 11:26 am
Scott's code doesn't return the same results as your query every time.
Here's my test:
DECLARE @SearchId INT = 100 --Changed to 300 and 400
CREATE TABLE Customer(
CountyID int
)
INSERT Customer VALUES(1),(2),(3),(4)
CREATE TABLE County(
CountyID int
)
INSERT County VALUES(1),(2),(3)
CREATE TABLE SearchCriteria(
CountyID int,
SearchID int
)
INSERT SearchCriteria VALUES(1,100),(2,200),(4,400)
--Original query
SELECT *
FROM Customer
WHERE CountyId IN (
SELECT CASE
WHEN EXISTS (
SELECT CountyId
FROM SearchCriteria
WHERE SearchId = @SearchId
)
THEN SearchCriteria.CountyId
ELSE County.CountyId
END
FROM County
LEFT JOIN SearchCriteria
ON County.CountyId = SearchCriteria.CountyId
AND SearchCriteria.SearchId = @SearchId
)
--My option
SELECT *
FROM Customer
JOIN County ON Customer.CountyID = County.CountyID
WHERE Customer.CountyID IN (
SELECT SearchCriteria.CountyId
FROM SearchCriteria
WHERE SearchCriteria.SearchId = @SearchId
)
OR NOT EXISTS(
SELECT SearchCriteria.CountyId
FROM SearchCriteria
WHERE SearchCriteria.SearchId = @SearchId)
--Scott's option
SELECT Customer.*
FROM Customer
WHERE
NOT EXISTS(
SELECT 1
FROM SearchCriteria
WHERE
SearchCriteria.SearchId = @SearchId AND
SearchCriteria.CountyId <> Customer.CountyId
)
DROP TABLE Customer
DROP TABLE County
DROP TABLE SearchCriteria
March 25, 2015 at 12:05 pm
Is the only difference that yours is able to handle situations where Customer.CountyId has a record that isn't in County.CountyId? I have a non-nullable FK constraint between Customer and County, so I'm not concerned about that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply