Reputation: 211
I have the following (moderately epic query) which I have been writing
Select *
from
(Select
Salutation,
FirstName, LastName, FullName,
PhotoUrl, CountryCode, Email, Birthday,
Gender, HomePhone, M.MemberId, IDType, JoinDate,
HomeLocation, HomeLocationId,
Region.Name as RegionName,
M.MembershipId,
coalesce(case
when Package.PackageIsReoccuring = 1 then 'Recurring'
when Package.PackageIsSession = 1 then 'Paid In Full'
when membership.TotalPrice = 0 then 'Free'
when Package.PackagePayInFull = 1 then 'Paid In Full'
end, 'N/A') as PackageTerm,
coalesce(PackageType.Name, 'N/A') as PackageType,
coalesce(membershipstate.name, 'N/A') as MembershipState,
MembershipStartDate =
case
when membership.StartDate IS NULL
then ''
else CONVERT(varchar(50),membership.StartDate)
end,
MembershipEndDate =
case
when membership.EndDate IS NULL
then ''
else CONVERT(varchar(50),membership.EndDate)
end,
Region.Id as RegionId
from
(select
AspNetUsers.Salutation,
AspNetUsers.FirstName, AspNetUsers.LastName,
CONCAT (AspNetUsers.FirstName, ' ', AspNetUsers.LastName) as FullName,
AspNetUsers.PhotoUrl, AspNetUsers.CountryCode, AspNetUsers.Email,
AspNetUsers.Birthday, AspNetUsers.Gender,
AspNetUsers.HomePhone as HomePhone,
Member.Id as MemberId, Member.IDType, Member.JoinDate,
HomeLocation.Name as HomeLocation,
HomeLocation.Id as HomeLocationId,
(case when (select top 1 id from membership where membership.memberid = Member.id and (membership.membershipstateid = 1 or membership.membershipstateid = 6)) IS NULL Then (select top 1 id from membership where membership.memberid = Member.id order by membership.enddate desc) ELSE (select top 1 id from membership where membership.memberid = Member.id and (membership.membershipstateid = 1 or membership.membershipstateid = 6)) END) as MembershipId
from
AspNetUsers
join
Member on AspNetUsers.id = Member.aspnetuserid
join
Location as HomeLocation on Member.HomeLocationId = HomeLocation.id) as M
left join
Membership on M.MembershipId = Membership.Id
left join
Package on Membership.packageid = Package.Id
left join
PackageType on Package.packagetypeid = PackageType.Id
left join
MembershipState on Membership.membershipstateid = MembershipState.Id
left join
Region on Membership.RegionId = Region.Id) as Result
order by
Result.LastName desc
I have a final join table which I want to use which is a many-to-many relationship on Region. Region has a Join Table (RegionLocations) which is a join between Region and Locations.
With my query below I would like to get all results where the HomeLocationId = 2 OR he has a LocationId (from RegionLocations) which also contains 2. The RegionId is a nullable value and isn't always populated.
How can I get this? Do I need to return values into a CSV? This final hurdle is a battle..
Thanks
Upvotes: 0
Views: 58
Reputation: 350750
You could extend this:
left join
Region on Membership.RegionId = Region.Id) as Result
to this:
left join
Region on Membership.RegionId = Region.Id
where M.HomeLocationId = 2
or Region.Id in (select RegionId from RegionLocation where LocationId = 2)
) as Result
The fields MembershipStartDate
and MembershipEndDate
can be evaluated more concisely as:
COALESCE(CONVERT(varchar(50),membership.StartDate), '') as MembershipStartDate,
COALESCE(CONVERT(varchar(50),membership.EndDate), '') as MembershipEndDate,
The inner field MembershipId
is defined with three sub-queries in a case when
, which can be shortened to just one query. It uses in
instead of an or
condition, and puts it in the order by
clause in a way that gets the priority right:
(select top 1 id
from membership
where membership.memberid = Member.id
order by case when membership.membershipstateid in (1,6) then 0 else 1 end,
membership.enddate desc
) as MembershipId
Finally, if you just have an outer query that performs a select * from (...) order by
, then why not skip that outer query and perform that order by
on the inner query direcly?
Upvotes: 1