CODEWITHSUNDEEP
sql-server
user1122
user1122

Reputation: 307

how to select rows based on condition sql

I have table which contains the ids like -

id
o1
o2
o3

now another table contains price for these ids based on the country like

id    country     price
o1    IND         11
o1    US          10
o1    default     9
o2    default     2
o3    UK          4
o3    default     3

basically i have to write a stored procedure where i will pass the region as parameter. for example if region is UK then i want select rows as follows if there is match then select the row otherwise default row.

id       country        price
o1       default        9
o2       default        2
o3       UK             4

Can you please suggest a good and efficient way of writing in SQL?

Upvotes: 0

Views: 72

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use CROSS APPLY with TOP:

DECLARE @country VARCHAR(20) = 'UK'

SELECT  x.*
FROM tblId i
CROSS APPLY(
    SELECT TOP 1 *
    FROM tblPrice p
    WHERE
        p.id = i.id
        AND (p.country = @country OR p.country = 'default')
    ORDER BY
        CASE 
            WHEN p.country = 'default' THEN 1 
            ELSE 0
        END
)x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One method uses two left joins:

select ids.id, coalesce(p.country, d.country) as country,
       coalesce(p.price, d.price) as price
from ids left join
     prices p
     on ids.id = p.id and p.country = @country left join
     prices d
     on d.country = 'default';

I think a more interesting method uses cross apply:

select p.*
from ids cross apply
     (select top 1 p.*
      from prices p
      where p.id = ids.id and p.country in (@country, 'default')
      order by (case when p.country = @country then 1 else 2 end)
     ) p;

Upvotes: 0

Related Questions