Reputation: 4584
I'm stumped on a sql query.
In a query, I have a result set like so:
FooId Name Value SourceLevel SourceId RecordId
-----------------------------------------------------------------
1 'Foo' 10 1 0 1
1 'Foo' 25 3 1 2
2 'Bar' 33 1 0 3
To that query, I pass parameters @Level1Level, @Level2Level, @Level3Level, and @Level1Id, @Level2Id, @Level3Id
(no, these aren't real names, but they illustrate my point).
My query is trying to do a filter like this:
WHERE ((SourceLevel = @Level1Level AND SourceId = @Level1Id)
OR (SourceLevel = @Level2Level AND SourceId = @Level2Id)
OR (SourceLevel = @Level3Level AND SourceId = @Level3Id))
If I pass in parameters like so:
@Level1Level = 1, @Level2Level = 2, @Level3Level = 3
@Level1Id = 0, @Level2Id = 3, @Level3Id = 2
I would want recordIds 1 and 3 back.
But, if I pass in parameters like so:
@Level1Level = 1, @Level2Level = 2, @Level3Level = 3
@Level1Id = 0, @Level2Id = 3, @Level3Id = 1
I would want recordIds 2 and 3 back. Unfortunately, in the second case, I'm getting all 3 records back, which makes sense, because of the OR in my where clause. I can't figure out how to limit my result set to say "only choose SourceLevel 1 if I haven't already matched on SourceLevel 2 or 3".
Anyone have any thoughts, assuming this makes any sense?
To clarify: I want each FooId from my result set, but only the most specific FooId available, based on the SourceLevel parameters passed in.
Upvotes: 0
Views: 110
Reputation: 4584
Here's what I ended up doing:
WHERE
(
(SourceLevel = @Level3Level AND SourceId = @Level3Id)
OR
(
SourceLevel = @Level2Level AND SourceId = @Level2Id
AND NOT EXISTS (SELECT 'X' FROM SourceTable WHERE SourceLevel = @Level3Level And SourceId = @Level3Id AND FooId = SourceTable.FooId)
)
OR
(
SourceLevel = @Level1Level AND SourceId = @Level1Id
AND NOT EXISTS (SELECT 'X' FROM SourceTable WHERE SourceLevel = @Level3Level And SourceId = @Level3Id AND FooId = SourceTable.FooId)
AND NOT EXISTS (SELECT 'X' FROM SourceTable WHERE SourceLevel = @Level2Level And SourceId = @Level2Id AND FooId = SourceTable.FooId)
)
)
This seems to do the filtering I was after...sorry that the question was so confusing. :)
Upvotes: 2
Reputation: 4574
I don't see exactly the point and why it's a sql question. But if you want SourceLevel 1 (i call it SL1 now) only if you haven't yet SL2 or SL3...
why don't you try with this:
SL2 OR SL3 OR (NOT SL2 AND NOT SL3 AND SL1)
Upvotes: 0
Reputation: 53705
(SourceLevel = 1 AND SourceId = 0) match 1 and 3 records
(SourceLevel = 2 AND SourceId = 3) no matches
(SourceLevel = 3 AND SourceId = 1) match 2 record
All three records will be returned by the query.
Upvotes: 0