Secondary criteria if no exact match

I have a table which contains postcodes split into two sections (prefix & suffix). The table also contains part postcodes (e.g. just prefix, suffix is NULL)

I want to write a function that will return either the record which matches will both parts or if nothing found returns a match against the prefix only.


For example

ID               Prefix                 Suffix
1                 SS1                    2EF
2                 SS1                    6TF
3                 SS1                    NULL

If I search for Prefix = 'SS1', Suffix = '2EF' the query returns 1
If I search for Prefix = 'SS1', Suffix = '7GS' the query returns 3
Daniel GradwellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT ID
FROM TableName
WHERE Prefix = 'SS1' 
    AND (Suffix = '7GS' OR Suffix IS NULL)

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
The easiest thing you could do is return 1 and 3 in the first case and only 3 in the second case via

SELECT ID FROM yourtable WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix

Bye, Olaf.
0
Olaf DoschkeSoftware DeveloperCommented:
This works:

Declare @data as Table (ID int, Prefix char(3), Suffix char(3) Null);

insert into @data values (1, 'SS1', '2EF'), (2, 'SS1', '6TF'), (3, 'SS1', NULL);

declare @Prefix as char(3);
declare @Suffix as char(3);

Set @Prefix = 'SS1';
Set @Suffix = '2EF';

SELECT Min(ID) as ID FROM @data WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix Group By Prefix;

Set @Suffix = '7GS';

SELECT Min(ID) as ID FROM @data WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix Group By Prefix;

Open in new window


The only condition is the NULL record always is the one with highest ID per Prefix.

Bye, Olaf.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Daniel GradwellAuthor Commented:
Thanks for your help on this however I can not guarantee that the NULL with have the highest ID. Any other suggestions would be greatly appreciated.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have more than a NULL value by Prefix? If not, don't bother with this.
Just in case you have any index on Suffix column, any use of function on that field won't use the index for the filter and that's why I provided a solution with ISNULL instead of COALESCE or ISNULL functions.
0
Daniel GradwellAuthor Commented:
The table has a unique combined key between Prefix and Suffix meaning only one NULL Suffix per Prefix.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you're good with these solutions.
0
Olaf DoschkeSoftware DeveloperCommented:
Well,

first this would return the best pair of Prefix and Suffix:
SELECT Prefix, Max(Suffix) as BestSuffix FROM @data WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix Group By Prefix

Open in new window


Now this needs to be matched with both fields to find the corresponding ID, but join conditions on NULLs are not as easy, so it becomes this mess:

Declare @data as Table (ID int, Prefix char(3), Suffix char(3) Null);

insert into @data values (1, 'SS1', '2EF'), (2, 'SS1', '6TF'), (3, 'SS1', NULL);

declare @Prefix as char(3);
declare @Suffix as char(3);

Set @Prefix = 'SS1';
Set @Suffix = '2EF';

Select ID From @data as TheData Inner Join 
(SELECT Prefix, Max(Suffix) as BestSuffix FROM @data WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix Group By Prefix) BestMatch
on TheData.Prefix = BestMatch.Prefix and ((TheData.Suffix is NULL AND BestMatch.BestSuffix IS NULL) OR TheData.Suffix = BestMatch.BestSuffix)

Set @Suffix = '7GS';

Select ID From @data as TheData Inner Join 
(SELECT Prefix, Max(Suffix) as BestSuffix FROM @data WHERE Prefix=@Prefix AND Coalesce(Suffix,@Suffix)=@Suffix Group By Prefix) BestMatch
on TheData.Prefix = BestMatch.Prefix and ((TheData.Suffix is NULL AND BestMatch.BestSuffix IS NULL) OR TheData.Suffix = BestMatch.BestSuffix)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
By the way, if - as I assume - there are just few records per prefix the usage of the combined key to also find the suffix is less important and the coalesce expression nicely puts both conditions on the suffix in one, but you can of course change that part according to Vitor, if you think it matters.

I avoided using Coalesce in the join condition, though it's also possible with something like on TheData.Prefix = BestMatch.Prefix and Coalesce(TheData.Suffix,'null') = Coalesce(BestMatch.BestSuffix,'null'), but that's just waiting for the case of 'null' stored in a suffix as string instad of the NULL value.

Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.