How to use Query Builder to have a left join inside a right join

@stanhook get yourself a visual query tool, I use ms access via odbc, but another decent one is SQLeo. But really I would look at ways of normalizing the data where you don't need such joins.

Have you viewed some of the free lessons from here on related data.

@jlrdw Thanks for the info, I will take a look!

Unfortunately, this is an existing query for a site I am converting using Laravel. This site is 10+ years old and there is no way I can tell to figure out exactly what the original developer did and the logic used to build the query. There are so many tables and things going on with their queries that the best thing to do is convert what is there to offer the best chance to return the same results as the current site. So I don’t have much of a choice and need to find a way to get it done.

@stanhook again, if the query works in the existing system then don't try to convert it, rather use as is, for many queries I use getPdo() and write regular queries.

@jlrdw Thanks for the help, but I can't use the existing query. I have to build the query by adding conditional where clauses based on data values retrieved from the URL.. For example:

if( $map != null )
->where($map->MapScopeCode == 'GEN');

I realize that may not be right, but that is the idea. I have a bunch of those to make. I can't do that with the typical query. And it isn't a query I made, it is one I have to convert.

The original in ASP SQL:

sql = "SELECT tblMap.Site, IIf([tblmap].[mapelec],'Digital Maps',IIf([tblmap].[mappaper],'Paper Maps','Other Maps')) AS MapType, tblMap.MapScopeCode, tblMap.MapScopeNum, IIf(([tblMap]![MapPlan]=True),'Plan View','') AS MapPlan, tlkpMapProfTyp.MapProfTypDesc, tlkpMapView.MapView, tblMap.MapNum, tblMap.MapTitle, IIf(([tblMap]![MapPL]=True),'Map includes point-located artifacts/samples','') AS PL, IIf(([tblMap]![MapCompos]=True),'Map is a composite of multiple source maps','') AS Composite, tblMap.MapComm"
    sql = sql & " FROM tlkpMapView RIGHT JOIN (tblMap LEFT JOIN tlkpMapProfTyp ON tblMap.MapProfTyp = tlkpMapProfTyp.MapProfTyp) ON tlkpMapView.MapViewCode = tblMap.MapViewCode"
    sql = sql & WC

WC is:

WC = " WHERE (((tblMap.mapnum)<>514)) AND ((tblMap.Site)='" & request("Site") & "')"
    If request("MapType").Count > 0 Then
    If request("MapType").Count > 0 Then
    WC = WC & " AND ((IIf([tblmap].[mapelec],'Digital Maps',IIf([tblmap].[mappaper],'Paper Maps','Other Maps')))='" & request("MapType") & "')"
End If
    If len(trim(request("MapScopeCode"))) > 0 Then
    WC = WC & " AND ((tblMap.MapScopeCode)='" & request("MapScopeCode") & "')"
    Else
    WC = WC & " AND ((tblMap.MapScopeCode) is Null)"

And more. If I could get that rightJoin in the QB to work, I would be golden. Thanks for your help!

@stanhook mysql has an if and case statement. So use some trial and error to work it out.

It is doable. Again I suggest regular SQL for this and not query Builder. But just a suggestion.

@jlrdw So here is my regular SQL statement:

SELECT
tblMap.Site,
CASE
    WHEN tblmap.mapelec = 'TRUE' THEN 'Digital Maps'
    WHEN tblmap.mappaper = 'TRUE' THEN 'Paper Maps'
    ELSE 'Other Maps'
END AS MapType,
tblMap.MapScopeCode, tblMap.MapScopeNum,
CASE
    WHEN tblmap.mapplan = 'TRUE' THEN 'Plan View'
END AS MapPlan,
tlkpMapProfTyp.MapProfTypDesc, tlkpMapView.MapView,
tblMap.MapNum, tblMap.MapTitle,
CASE
    WHEN tblmap.MapPl = 'TRUE' THEN 'Map includes point-located artifacts/samples'
END AS PL,
CASE
    WHEN tblmap.MapCompos = 'TRUE' THEN 'Map is a composite of multiple source maps'
END AS Composite,
tblMap.MapComm
FROM tlkpMapView
RIGHT JOIN (tblMap LEFT JOIN tlkpMapProfTyp ON tblMap.MapProfTyp = tlkpMapProfTyp.MapProfTyp)
    ON tlkpMapView.MapViewCode = tblMap.MapViewCode
WHERE tblMap.mapnum <> '514' AND tblMap.Site = '5mt11842'
        AND CASE
                WHEN tblmap.mapelec = 'TRUE' THEN 'Digital Maps'
                WHEN tblmap.mappaper = 'TRUE' THEN 'Paper Maps'
                ELSE 'Other Maps'
            END = 'Digital Maps'
        AND tblMap.MapScopeCode = 'Architectural block'
        AND tlkpMapProfTyp.MapProfTypDesc = ''
        AND CASE
                WHEN tblmap.mapplan = 'TRUE' THEN 'Plan View'
            END = 'Plan View'
ORDER BY tblMap.MapScopeCode,
    CASE WHEN tblmap.mapplan = 'TRUE' THEN 'Plan View' END,
    tlkpMapProfTyp.MapProfTypDesc, tblMap.MapNum

I can get everything I need in the where clause from the URL. But, the URL can have empty variables so I have in the above statement:

 AND tlkpMapProfTyp.MapProfTypDesc = ''

Because the URL has: &MapProftypDesc=& - nothing in MapProftypDesc.

So - AND tlkpMapProfTyp.MapProfTypDesc = NULL does not work.

How would I handle missing values?

@stanhook see https://stackoverflow.com/questions/11480763/how-can-i-get-parameters-from-a-url-string

And do what you need. How did the original query deal with a blank value, that's what you need to figure out.

@jlrdw Thanks, I will take a look.

They used the where clause to check if it was empty. I pasted it above and here:

WC = " WHERE (((tblMap.mapnum)<>514)) AND ((tblMap.Site)='" & request("Site") & "')" If request("MapType").Count > 0 Then If request("MapType").Count > 0 Then WC = WC & " AND ((IIf([tblmap].[mapelec],'Digital Maps',IIf([tblmap].[mappaper],'Paper Maps','Other Maps')))='" & request("MapType") & "')" End If If len(trim(request("MapScopeCode"))) > 0 Then WC = WC & " AND ((tblMap.MapScopeCode)='" & request("MapScopeCode") & "')" Else WC = WC & " AND ((tblMap.MapScopeCode) is Null)"

@kokoshneta Oh, I see what you are saying. I had gotten to original query to work but I can try this on something else. I have a bunch to do. Thanks!