zxc

mail@pastecode.io avatar
unknown
abc
2 years ago
578 B
4
Indexable
Never
declare @categoryID int = 238;

with cats as(
	select c.id, parentID Pid
	from BossCategories c 
	where id = @categoryID
	union all
	select c1.id, parentID
	from BossCategories c1
		inner join cats on c1.id = Pid
)
, uids as (
	select userid ids
	from [dbo].[BossCountryUsers] u 
	where exists(select * from cats where countryid = id)
)
select Users.* , c.CountryName , Firstname +' ' + lastname as FullName   
from Users  
	inner join Countries c on c.CountryId = Users.Country
where exists(select * from uids where userid = ids)
order by FirstName,LastName