Advanced SQL JOIN with CASE (Feel free to give any ideas)
Question
Application Type
Reactive

Hi all,


Currently, I'm having issues with Advanced SQL since I'm quite new to advanced SQL myself. I wanted to LEFT JOIN the table under specific conditions. For example, when the End-User selected a certain filter, the Main Table will be LEFT JOINed with a specific part of another table. I've done some researches on SQL but still don't really sure how the CASE works in my situation. 


           
SELECT {TM_Accommodation}.[Id], {Region}.[Region], {OfficeLocation}.[Location], {TM_Report}.[StaffName], {TM_AccommodationStatus}.[Label], {TM_Accommodation}.[PropertyName], {TM_Landlord}.[LandlordName], 
{TM_Accommodation}.[AgreementStartDate], {TM_Accommodation}.[AgreementEndDate], {TM_RentedPropertyType}.[Label], {TM_Accommodation}.[RentalPerMonth], {Action}.[Label], {TM_Accommodation}.[CurrentTenant]

FROM (((((((({TM_Accommodation}

LEFT JOIN {TM_AccommodationStatus} ON ({TM_Accommodation}.[StatusId] = {TM_AccommodationStatus}.[Id]))
LEFT JOIN {TM_Landlord} ON ({TM_Accommodation}.[LandlordId] = {TM_Landlord}.[Id]))
LEFT JOIN {TM_RentedPropertyType} ON ({TM_Accommodation}.[RentedPropertyTypeId] = {TM_RentedPropertyType}.[Id]))
LEFT JOIN {Region} ON ({TM_Accommodation}.[Region] = {Region}.[Id]))
LEFT JOIN {Action} ON ({TM_Accommodation}.[SharingBasisId] = {Action}.[Id]))
LEFT JOIN {TM_Report} ON ({TM_Accommodation}.[Id] = {TM_Report}.[AccommodationId]) AND ({TM_Report}.[ReportStatusId] = @TMReportStatusActive  OR {TM_Report}.[ReportStatusId] = @TMReportStatusExpiring ))
LEFT JOIN {OfficeLocation} ON ({TM_Accommodation}.[Location] = {OfficeLocation}.[Id]))
LEFT JOIN {TM_AccommodationRoom} ON ({TM_Accommodation}.[Id] = {TM_AccommodationRoom}.[AccommodationId]) AND 

(CASE
    WHEN @VacantId = @ActionYes AND @SharingBasisId  = @ActionYes AND {TM_Accommodation}.[SharingBasisId] = @ActionYes THEN {TM_AccommodationRoom}.[TenantName] = ''
    WHEN @VacantId = @ActionYes AND @SharingBasisId = @ActionNo AND {TM_Accommodation}.[SharingBasisId] = @ActionNo THEN {TM_AccommodationRoom}.[TenantName] = ''
    WHEN @VacantId = @ActionNo AND @SharingBasisId = @ActionYes AND {TM_Accommodation}.[SharingBasisId] = @ActionYes THEN GROUP BY {TM_AccommodationRoom}.[AccommodationId] HAVING COUNT({TM_AccommodationRoom}.[TenantName] = '') < 1
    WHEN @VacantId = @ActionNo and @SharingBasisId = @ActionNo AND {TM_Accommodation}.[SharingBasisId] = @ActionNo THEN {TM_AccommodationRoom}.[TenantName] <> ''
    ELSE @VacantId  IS NULL OR @SharingBasisId IS NULL
END))
    
WHERE ({TM_Landlord}.[LandlordName] LIKE @SearchKeyword OR {TM_Accommodation}.[FullAddress] LIKE @SearchKeyword OR {TM_Report}.[StaffName] LIKE @SearchKeyword) 
AND ({TM_Accommodation}.[RentedPropertyTypeId] = @PropertyTypeId  OR @PropertyTypeId IS NULL)
AND ({TM_Accommodation}.[SharingBasisId] = @SharingBasisId OR @SharingBasisId  IS NULL)
AND ({TM_Accommodation}.[Region] = @RegionId OR @RegionId IS NULL)
AND ({TM_Report}.[DepartmentTo] =@DepartmentId or @DepartmentId IS NULL)
AND ({TM_Accommodation}.[AccommodationTypeId] = @AccommodationStatusId  OR @AccommodationStatusId  IS NULL)
AND ({TM_Accommodation}.[AccommodationTypeId] = @AccommodationTypeId or @AccommodationTypeId IS NULL)
AND ({TM_Accommodation}.[IsActive] = 1)
AND ((@DateFrom IS NULL AND @DateTo IS NULL) 
    OR (@DateFrom IS NULL AND @DateTo IS NOT NULL AND {TM_Accommodation}.[AgreementEndDate] <= @DateTo) 
    OR (@DateFrom IS NOT NULL AND @DateTo IS NULL AND {TM_Accommodation}.[AgreementEndDate] >= @DateFrom) 
    OR (@DateFrom IS NOT NULL AND @DateTo IS NOT NULL AND @DateFrom <= @DateTo AND {TM_Accommodation}.[AgreementEndDate] >= @DateFrom and {TM_Accommodation}.[AgreementEndDate] <= @DateTo)
    OR (@DateFrom IS NOT NULL AND @DateTo IS NOT NULL AND @DateFrom > @DateTo))
AND 
(CASE
    WHEN (@VacantId = @ActionYes THEN ({TM_Report}.[Id] IS NULL AND {TM_AccommodationRoom}.[TenantName] = '' AND {TM_Accommodation}.[CurrentTenant] = '') 
        OR (CASE
                WHEN @SharingBasisId = @ActionYes THEN {TM_AccommodationRoom}.[TenantName] = ''
                ELSE {TM_Accommodation}.[CurrentTenant] = ''
            END)
           AND {TM_Accommodation}.[CurrentTenant] = '')
    WHEN (@VacantId = @ActionNo THEN TM_REPORT.ID    IS NOT NULL 
        OR (CASE
                WHEN @SharingBasisId = @ActionYes THEN TM_ACCOMMODATION.SHARINGBASISID = @ActionYes AND {TM_AccommodationRoom}.[TenantName]   <> ''
                ELSE {TM_Accommodation}.[CurrentTenant] <> ''
            END))
    ELSE @VacantId IS NULL
END)  

I've tried to use HAVING to check if there's any VACANT, the record will only be LEFT JOINed if there's no VACANT? Then in the filter I will check for the room with NO VACANT for the listing.


In the SWITCH Case, it should be joining to another table with certain conditions met. For example, if the End-User selected VACANT = NO and SharingBasis = Yes, it should (not be joining the table)? Or Should join the table and we do the filtering later? But then, if we used LEFT JOIN, it will ignore the other record under the same Id.


For example,

TableA having House.A

TableB having House.A as reference key and Room.A(Vacant), Room.B(Vacant), Room.C(Occupied).


If we LEFT JOIN with TableB.Room <> VACANT, it will LEFT JOIN RoomC(Occupied). But in the filter, it only detects RoomC but not RoomA(VACANT) and RoomB (VACANT). Under this condition, this house should not be considered as NOT VACANT because there's  still Room with VACANT. 


Any idea on this would be very much appreciated. Hope to learn more on Advanced SQL and Logics.


Regards,

Kenny


EDIT: 

For the update, I've managed to change the CASE to something like this and its working with another condition other than the underlined code:

But the underlined code is still having some logic issue. Any advice would be much appreciated. Hopefully, this post can help others who might be having a similar issue.


Regards,
Kenny

Hi Kenny,

As far as I can see you have done a lot of work to create this query. Thank you for giving a simplified example.

So you have an Entity House, an Entity Room, an entity RoomStatus a composite entity HouseRoom containing the attributes HouseId, RoomId and RoomStatusId.

Your use case is that you would like to search for Houses containing Vacant Rooms.
You can realize that by creating an aggregate On House and HouseRoom, Filtered on HouseRoom.RoomStatusId = Entities.RoomStatus.Vacant, grouped by HouseRoom.HouseId having a count > 1.

When filtering on Entities.RoomStatus.Occupied you'll fetch the fully Occupied Houses.

I think the main issue is that your SQL is too dynamic to keep it easily testable or maintainable. What you could do is to split up the Sql into multiple way more simple SQLs (or preferable aggregates) that support different use cases.
Please feel free to ask more questions.

good luck,

Hans

Hi Hans,


Thanks for replying to my thread. In this case, there are only two entities involved, which are Entity House and Entity Room. Inside Entity House, it contains all the information regarding the House and inside Entity Room, it contains the name of the tenant (consider as RoomStatusId), if Empty, then IsVacant, else not IsVacant. 

Edit:  "Your use case is that you would like to search for Houses containing Vacant Rooms.
You can realize that by creating an aggregate On House and HouseRoom, Filtered on HouseRoom.RoomStatusId = Entities.RoomStatus.Vacant, grouped by HouseRoom.HouseId having a count > 1."

You're right on this part. When doing the filter, I need to get the fully Occupied Houses by checking on the RoomStatus(fully occupied) by making sure the table joined is correct (Filtered on HouseRoom.RoomStatusId = Entities.RoomStatus.Vacant, grouped by HouseRoom.HouseId having a count > 1 )

" What you could do is to split up the SQL into multiple way more simple SQLs (or preferable aggregates) that support different use cases. "

What I can do now is that to split those into smaller parts to test before joining together? I need to join all those aggregates together because the End-User will have several filters that will filter the attribute from different tables. 

Currently, I removed the underlined part and it's working fine without the WHERE condition (for testing). I haven't change the usage of CASE under the WHERE but they should be similar to what I did under LEFT JOIN. 

Do appreciate it if there is any way to prevent the SQL from being too dynamic while being able to achieve the objective. Thanks.


Regards,

Kenny


Just for the update, maybe I should just add a flag to check if there's an empty room on a non-sharing basis and any empty room under sharing basis, instead of joining the table which makes stuff complicated. I think this approach is much easier to achieve, which will not affect the overall performance as well. Thanks for your suggestion tho! Cheers!


Regards,

Kenny

a way to achieve this is to take a look at the filter which causes the biggest change in your sql. e.g. if it is the filter 'Occupied ' yes/no, than you can split up your big sql into 2 sqls, one for occupied yes and one for occupied no. Based on the filter-value you run the first or second SQL. You have to assign the resultlist to the list that is used in your screen/webblocks. 

Of course you might have more SQL's to change when requirements change, but the benefit is that it is really clear what each SQL will do, so you are able to change it faster and test it easier (which is simply said the benefit to have more specific code versus more generic code).

And.. you might replace your SQLs to Aggregates which will give you more benefits according the maintainability of SQLs. 

Kind regards

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.