Mysql Left Join Case When-CodePudding

I have the following mysql table fields

|action_id|timestamp|user|module|action|object|info|interface|

Content of which are below:

69    |2021-12-06 22:15:43|1 |CATALOGUING|ADD|24   |item  |intranet    
122058|2022-02-23 09:47:17|13|CATALOGUING|ADD|10338|biblio|intranet

Here is the mysql query that I am working on:

SELECT action_id, i.barcode, CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',b.title,'</a>') AS Title, biblio.author, al.timestamp, CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',borrowers.borrowernumber,'\">',borrowers.surname, ', ', borrowers.firstname,'</a>') AS Librarian, i.permanent_location, (CASE WHEN al.info LIKE "biblio%" THEN 'biblio' ELSE 'item' END) as typeofentry, al.action as type_of_action, al.info from action_logs AS al LEFT JOIN borrowers ON al.user =
CASE WHEN al.info LIKE "biblio%" THEN 
borrowers.borrowernumber LEFT JOIN biblio on al.object=biblio.biblionumber
ELSE borrowers.borrowernumber LEFT JOIN items AS i on al.object=i.itemnumber LEFT JOIN biblio on i.biblionumber=biblio.biblionumber
END

I am trying to use CASE WHEN for LEFT JOIN such that if the "info" column is biblio, the query should left join with biblio and ignores LEFT JOIN with item table. If the "info" column is item, then it should LEFT JOIN with item then LEFT JOIN with biblio. Is this even possible with LEFT JOIN. This is what I have been trying to emulate: How to use a case statement to determine which field to left join on

CodePudding user response：

You can't do a case/when join as you have here. Since you have a split way of doing the HTML link references, and each has its own context of a biblio record, you might be best to get the pieces from two distinct queries of biblio vs non-biblio and UNION them. Take that result and apply to your concat process. Something like

SELECT 
        PQ.action_id, 
        PQ.barcode, 
        CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
                PQ.biblionumber, '\">', b.title, '</a>') Title, 
        PQ.author, 
        PQ.timestamp, 
        CONCAT( '<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', 
                b.borrowernumber, '\">', b.surname, ', ', 
                b.firstname, '</a>') Librarian, 
        PQ.permanent_location, 
        PQ.typeofentry, 
        PQ.action as type_of_action, 
        PQ.info 
    from
        (
        SELECT 
                action_id, 
                i.barcode, 
                biblio.biblionumber,
                biblio.author, 
                al.timestamp, 
                i.permanent_location, 
                'biblio' typeofentry, 
                al.action, 
                al.info,
                al.user
            from 
                action_logs al 
                    LEFT JOIN items i 
                        on al.object = i.itemnumber 
                    LEFT JOIN biblio 
                        on al.object = biblio.biblionumber
            where
                al.info like 'biblio%'
        UNION ALL
        SELECT 
                action_id, 
                i.barcode, 
                biblio.biblionumber,
                biblio.author, 
                al.timestamp, 
                i.permanent_location, 
                'item' typeofentry, 
                al.action, 
                al.info,
                al.user
            from 
                action_logs al 
                    LEFT JOIN items i 
                        on al.object = i.itemnumber 
                        LEFT JOIN biblio 
                            on i.biblionumber = biblio.biblionumber
            where
                NOT al.info like 'biblio%'
        ) PQ
            LEFT JOIN borrowers b
                ON PQ.user = b.borrowernumber