Doesn't that depend on the cursor? They aren't always evil, ... they're just often not necessary... However, that said, I'm still waiting on the OP to come back with something more cogent about the desired results before I'll commit to code..
ScottPletcher (7/10/2014)
sgmunson (7/10/2014)
That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess such as that might be counterproductive in the long run.ScottPletcher (7/10/2014)
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or multiples, but this should handle either.
SELECT DISTINCT c.*
FROM Cars c
INNER JOIN CarSearchCriteria scs ON
scs.CarId = c.ID OR
(scs.CarId IS NULL AND C.ColorId = scs.ColorId)
WHERE
scs.CarSearchId IN (<list_of_search_id(s)_to_do>)
"Nasty" is relative. I'd take any query plan I get from that vs. using a cursor :-).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)