CODEWITHSUNDEEP
sqldatabasesql-server-2008
nsivakr
nsivakr

Reputation: 1595

Sql server query that is recursive in multiple tables

I've a table structure as follows with sample data as follows Name: User

User   Region
Sam    USA    
Meyer  NA    
Ying   Japan    
Yang   Asia

I also have another table, with the region and the countries

Name: Region

       Region Country         RegionList    
        USA     America            
        NA       NULL          USA            
        NA      Canada            
        NA      Mexico            
        Japan   Japan
        Asia     NULL         Central        
        Asia      NULL         East        
        Central  India        
        East     Dubai
        Central  Japan

How do you write a SQL server query to list each user and the corresponding countries, that he/she represents?

Like:

Sam   America
Meyer America
Meyer Canda
Meyer Mexico
Ying  Japan
Yang  Japan
Yang  India
Yang  Dubai

Upvotes: 1

Views: 2889

Answers (3)

flup
flup

Reputation: 27104

If your Region table is potentially infinitely many levels deep, you can use a Common Table Expression. See Recursive Queries Using Common Table Expressions.

WITH UserRegions (username, region, country)
AS
(
    -- Anchor member definition
    SELECT u.[user] AS username, r.regionlist AS region, r.country
    FROM [User] u
    JOIN Region r
        ON u.Region = r.Region
    UNION ALL
    -- Recursive member definition
    SELECT ur.username, r.regionlist AS region, r.country
    FROM Region r
    INNER JOIN UserRegions AS ur
        ON r.Region = ur.Region
)
-- Statement that executes the CTE
SELECT username, country
FROM UserRegions
WHERE country IS NOT NULL
ORDER BY username

http://sqlfiddle.com/#!3/ca9de/8

Upvotes: 5

Matt Busche
Matt Busche

Reputation: 14333

You can use a JOIN to get your desired results

SELECT user, isNull(Country,RegionList) Country
FROM user
    JOIN region ON users.region = region.region
ORDER BY user, Country

if you want to return all users even if they don't have any countries then you can use a LEFT OUTER JOIN

SELECT user, isNull(isNull(Country,RegionList),'No countries') Country
FROM user
    LEFT OUTER JOIN region ON users.region = region.region
ORDER BY user, Country

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

Based on the explicit schema and sample data you provided:

select
user, isnull(region.country, r2.country) as Country 
from 
user
left join 
region 
on user.region = region.region
left join
region r2
on r2.region = region.regionlist

Upvotes: -1

Related Questions