Christopher Pettigrew
Christopher Pettigrew

Reputation: 211

SQL Where in for Many to Many Join Table

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

Answers (1)

trincot
trincot

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

Some other remarks about your query:

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

Related Questions