A couple of weeks a go I wrote a quick query to pull some data for me, something has been bothering me, is there a better way to find specific variants of something... Quick example:
SELECT FirstName, Surname, Quals1, Quals2, Quals3
FROM Employees
WHERE (
Quals1 = @WantedQuals
OR
Quals2 = @WantedQuals
OR
Quals3 = @WantedQuals
)
OK, for instance, if @WantedQuals = 'A Levels' then it would return data if any of the Quals = 'A Levels'
But what if @WantedQuals = 'Level 3' is there an easy way to make it return for 'Level 1', 'Level2' & 'Level 3' but not 'Level 4', 'Level 5' or 'Level 6'....?
If memory serves me correctly I previously achieved it by using a case statement but it seems a bit of an ugly, dirty solution. Just wondering if there was a more eligant solution available...?
Part of the problem is a bad table design. You shouldn't have text like 'Level n'. What if someone mistypes it and enters 'levle 42'? Really you want an FK to a qualification table instead.
But to answer the question directly. If you guarantee that the text will always be of the form "Level n", never two digits. then you can ask WHERE Quals1 >= @WantedQuals
Without those guarantees, or better a separate table with the qualifications in it, you run the risk of missing something.
Thanks for your reply, I'd just like to say this isn't in a production environment it's just something a bit similar to something I came accross a good few weeks back, purely from wanting to learn and get better wondered the best way of querying it....
Anyhow, apologies I may not have explained it very well. The problem is there are quite a few enteries, only about 25% of them are in the form of "Level n". I'm not sure how SQL applies numeracy against characters, i would guess alphabetically? ie <='C' would be 'A' 'B' & 'C'...?
With out checking my test DB I can't remember exactly what is contained in the fields but an example list would be:
Maths
Science
English
History
Geography
Level 1
Level 2
Level 3
Level 4
Level 5
The idea being that if I was searching for 'Geography' then only geography would be output, but if searching for 'Level 3' then then Level's 1, 2 & 3 would be output, the thinking behind it is that if you're a level 3, you've already done level 1 & 2....