Mysql Left Join Case When

Question

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

How to "conditionally join" like this is a faq. But one must clearly say what wants in a generic way in many phrasings in order to search effectively. PS [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) PS We don't conditionally join, we join, getting every combination of a row from 2 tables, plus for left join any unmatched left table rows extended by nulls, then we select the rows that we want from the rows after the joins, possibly using cases. Look at the input & intermediate & final output of a [mre]. — philipxy, Mar 02 '22 at 04:02

score 1 · Accepted Answer · answered Mar 02 '22 at 08:15

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

Wow! This seem to be going the right way, with little modification. I was of the impression that I can do it via CASE WHEN, so I was wrong, such that further searches won't yield me what I want. Will take a mental note and further read UNION ALL. Thanks again! — schnydszch, Mar 02 '22 at 14:52
@schnydszch, thing to note. When doing a union, no matter how many (you can have more than just a single union), the key point is that each query OF the union must contain the same content, count of columns, AND same order of the columns. They basically join into the same one result set and can then be used as one result alias (I used PQ as PreQuery alias) for the rest of the query. — DRapp, Mar 02 '22 at 14:56

Mysql Left Join Case When

1 Answers1