CODEWITHSUNDEEP
sqlsql-server
webdad3
webdad3

Reputation: 9080

Dynamic SQL Case statement?

I have a table that is called DefaultCountries.

It contains the following columns:

I have 29 different countries with varying data for each country. For example:

CountryID of 1 has 2 records:

CountryID1

CountryID of 7 has 3 records

CountryID7

I have a table variable @CountryData that I'm trying to join to that currently has data for CountryIDs 1 and 10.

Example Table

I would like to be able to match up to the DefaultCountryID found in the DefaultCountries table. So if I pass in CountryID = 1, I could do something like:

SELECT TOP 1 DefaultCountryID 
WHERE CountryID = 1     -- that is my simple case.

Where I'm having an issue is where there are more than 2+ rows. As in my example for CountryID = 7. Basically I want to have something where if there is no records in my table that I'm trying to join to with the 1st record (CountryRank = 1) then I would like it to search for the next record.

So searching for CountryID = 7, then if nothing, then CountryID = 10 (so on and so forth).

I've tried different variations of this LEFT JOIN

SELECT * 
FROM @CountryData cd
LEFT JOIN DefaultCountries dc ON dc.defaultCountryid = cd.CountryID
                              AND cd.CountryID = 7

But this just brings back all the data (1 & 10). Do I have to use a loop to do this the way I'm describing? I thought about a CASE statement as well, but since I could have more than 2+ in the DefaultCountries table I'm not sure how I could keep searching.

I tried doing an IN statement, but I have 2 issues there. I can't do an order by in the IN statement as I get the following message:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

And even if there is a way to make sure I'm searching in the order I want using an IN statement, I want to make sure it stops searching on the 1st one it finds. So to expand on my example, if table (@CountryData) table now contains records where CountryID = 7 and CountryID = 10 I only want to return the records where CountryID = 7.

Upvotes: 0

Views: 170

Answers (1)

S3S
S3S

Reputation: 25112

If i'm following you right you can just use a CTE

with cte as(
select
    CountryID,
    DefaulteCountryID,
    ROW_NUMBER() over (partition by CountryID order by CountryRank) as rn
from DefaultCountries
where DefaultCountryID in (select distinct CountryID from @CountryData))

Then just join to this cte where rn = 1

So this would satisfy "Basically I want to have something where if there is no records in my table that I'm trying to join to with the 1st record (CountryRank = 1) then I would like it to search for the next record" meaning if there wasn't a 1 you'd get 2, or 3, or what ever the next order number is.

Upvotes: 1

Related Questions