CODEWITHSUNDEEP
sql
Jonas
Jonas

Reputation: 4584

Sql query confusion

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

Answers (3)

Jonas
Jonas

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

bw_üezi
bw_üezi

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

Andrew Orsich
Andrew Orsich

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

Related Questions